Find Orphaned DTA Service Instances with SQL Query
I was trying to find what messages were being orphaned, so I started doing a little digging to figure out how I could get more detail.
With that being said, I still use the Terminator to fix the issue. ?I do NOT recommend updating or changing any messages in the messagebox without consent from Microsoft, as doing so could violate your support agreement.
This SQL query will give you the Orphaned Service Instances in the tracking database.
SELECT * FROM BizTalkDTADb.dbo.dta_ServiceInstances (NOLOCK) srvi JOIN BizTalkDTADb.dbo.dta_Host(NOLOCK) host ON srvi.nHostId = host.nHostId WHERE dtEndTime IS NULL AND uidServiceInstanceId NOT IN ( SELECT uidInstanceID FROM BizTalkMsgBoxDb.dbo.Instances (NOLOCK) UNION SELECT StreamID FROM BizTalkMsgBoxDb.dbo.TrackingData (NOLOCK))
Why does this happen? Well, first, you have tracking enabled for the port or orchestration. The above query will display the error column, which could give you which port and/or orchestration.