0

Find Orphaned DTA Service Instances with SQL Query

Posted by Chris Straw on October 26, 2013 in BizTalk, SQL Server |

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.

Tags: ,

Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright © 2011-2019 Chris Straw All rights reserved.
This site is using the Desk Mess Mirrored theme, v2.5, from BuyNowShop.com.