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.