Orphaned messages in the BizTalk tracking database

Orphaned messages in the tracking database - where do they come from?

My SQL Agent job Monitor BizTalk Server was failing with the error Orphaned DTA Service Instances in BizTalkDTAdb, that is my tracking database.

Successful execution of this job signals that you have none of the following issues (so you do want it to run J):

·         Messages without any references

·         Messages without reference counts

·         Messages with reference count less than 0

·         Message references without spool rows

·         Message references without instances

·         Instance state without instances

·         Instance subscriptions without corresponding instances

·         Orphaned DTA service instances

·         Orphaned DTA service instance exceptions

·         TDDS is not running on any host instance when global tracking option is enabled.

Looking into this error I’ve realized (via tests) that such orphaned messages in the tracking database are actually created in a number of situations, for instance when:

·         there is no subscribers to a message received in a pipeline using the XMLReceive receive pipeline

·         the receive pipeline fails due to bad data

This post on the BizTalk Administrator Blog discusses possible resolutions to this error:

http://biztalkadmin.com/orphaned-messages-in-the-tracking-database/

First off all, if you wonder if you have any orphaned messages in the BizTalk database i recommend you to use the MessageBox Viewer this will show you the information you need, you can also use the following query:

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))

As you can see this will look for instances in the tracking database (BizTalkDTADb) where there is no end time. and then make sure the service instance id is not in the message box. It’s vital to use the “WITH (NOLOCK)” when querying towards the production SQL servers in order to make sure you don’t hold any looks.

What have happened? Well basically the message went out without informing the BizTalk tracking database that it is completed.

If you happened to experience a number above 2000 you should clean this. You can do it by using the Terminator toll or by running the following query:

NOTE: This is may violate Microsoft support agreement! Do it at your own risk.

USE [biztalkDTADb]
UPDATE [dbo].[dta_ServiceInstances]
SET [dtEndTime] = GetUTCDate()
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))

This query actually puts the time now as end time. It will not remove the orphaned messages until the purge and archive job has passed the desired time for keeping the messages.