Monitor biztalk server(biztalkmgmtdb) SQL Server Agent job failed on step 2 - Part2

Please go through: Monitor biztalk server(biztalkmgmtdb) sql agent job failed on step 2 - Part1 for details how this question was born.

How can we prevent messages with refcount less than 0, without reference counts from BizTalkMsgBoxDb and Orphaned DTA service instances from BizTalkDTADb?

Usually I need to run BHM quite frequently on my environment to clean up inconsistency so is there good practice to do regularly when ever SQL Server Agent job failed.

Solution

There are queries you can use in SQL that find if there are any orphaned messages in BizTalk. One of these queries is this one:

select count(*) from [BizTalkDTAdb].[dbo].[dta_ServiceInstances] 
where dtEndTime is NULL and [uidServiceInstanceId] NOT IN 
  (SELECT [uidInstanceID] FROM [BizTalkMsgBoxDb].[dbo].[Instances] WITH (NOLOCK)
    UNION
    SELECT [StreamID]
    FROM [BizTalkMsgBoxDb].[dbo].[TrackingData] with (NOLOCK))

FYI: I found this query here: https://www.biztalkadmin.com/orphaned-messages-in-the-tracking-database/

It will list the count of the orphaned service instances. Remove the COUNT clause from your SELECT statement to get the list. This might get you an idea as to where they come from and how to perhaps change your implementation to limit the occurrence.

Note that a few orphaned instances are not a big deal for your BizTalk environment. Anything lower than 2000 can be more or less dismissed.

Important: do not run any cleanup query without stopping the BizTalk host instances or it may violate your support agreement.