Remove DTA orphans in BizTalk

Standard disclaimer: This is officially not supported and you should never update a BizTalk database using T-SQL *wink* *nudge*

What are orphans?

When tracking is performed in BizTalk, it happens in several steps. The first thing that happens is that a row is created in the DTA database, the dta_ServiceInstances table to be specific. There is a bug in BizTalk that makes the “DTA Purge and Archive” job unable to delete the row. This creates an orphan and if this happens a lot the table will be filled with junk data.

But why?

When tracking starts the current date and time is placed in column dta_ServiceInstances.dtStartTime. In the same row, there is a column called dtEndTime. When tracking starts, this column gets a null-value. When tracking completes, the column is updated and the date and time of completion is set. If this does not happen, the job will not remove the row as it is considered active.

How to find out how many orphans there are

You are running BHM (BizTalk Health Monitor) so you know how many there are, right? But how many are too many? If your queries using BizTalk Administration Console times out, then there are too many.

Here is another way to find out how many there are, using SQL.

select

    count(*) as ‘NoOfOrphans’

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)

    )

 

This query clearly shows the number of unmarked instances but also matches the result to what instances are still active in the MessageBox.

If you want more information on each instance, replace count(*) with a simple * in the first row of the SQL script. If you do this, you can easily see that the data has a dtStartTime but no dtEndTime.

How do I remove them?

BizTalk Terminator tool

This is the supported way to remove the data. There is a very important caveat to using the tool: you must completely stop the environment. If this is a bad thing you can run the script executed by the terminator tool yourself.

T-SQL Script

A very simple script that will update the table by setting a endtime for all orphans, making it possible for the purge job to delete them.

BEGIN TRAN

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)

    )

— If it works: uncomment and run this row.

— Commit tran

— If it does NOT work: uncomment and run this row

— Rollback tran

The script will handle the exact same rows as in the first query. In order to make this update behave in the best way, use a transaction by following these steps:

  1. Run the first script that gets the number of rows, note the result.
  2. Run the second script (make sure to include the BEGIN TRAN at the start).
  3. Note the number of rows affected.
  4. If the numbers match up the script has run correctly, uncomment and run the COMMIT TRAN row.
  5. If the numbers dos not match up, something went wrong. Uncomment and run the ROLLBACK TRAN row to cancel the transaction.

NOTE! It is very important to run the COMMIT/ROLLBACK in the same query window as the main script.

The purge job

The next time the purge job runs, the number of orphans should decrease. Run the first script to make sure.