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 details how question born.

how can prevent messages refcount less 0, without reference counts biztalkmsgboxdb , orphaned dta service instances biztalkdtadb?

usually need run bhm quite on environment clean inconsistency there practice regularly when ever sql server agent job failed.

there queries can use in sql find if there orphaned messages in biztalk. 1 of these queries one:

select count(*) [biztalkdtadb].[dbo].[dta_serviceinstances]  dtendtime null , [uidserviceinstanceid] not in    (select [uidinstanceid] from[ biztalkmsgboxdb].[dbo].[instances] (nolock)     union     select [streamid]     [biztalkmsgboxdb].[dbo].[trackingdata] (nolock)) 

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

it list count of orphaned service instances. remove count clause select statement list. might idea come , how perhaps change implementation limit occurrence.

note few orphaned instances not big deal biztalk environment. lower 2000 can more or less dismissed.

important: not run cleanup query without stopping biztalk host instances or may violate support agreement.