Orphaned messages in the tracking database

Posted: October 4, 2011  |  Categories: Biztalk BizTalk Adminsitration Uncategorized Useful tips
Tags:

I came across a client whom had some issues with orphaned messages in the BizTalkDTADb (BizTalk Tracking database). The issue came after a health check with Microsoft. They were told to use Terminator to fix this.

You can download the entire article as a PDF document.
Orphaned Messages In The Tracking Database.

The issue with running the terminator tool is that you need to stop all hosts (plus IIS), stop all SQL server agent jobs, and backup the database. The problem they had was to do it. They did not get the opportunity to stop the entire production environment in order to clean out this problem.

 

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.

You can download the entire article as a PDF document.
Orphaned Messages In The Tracking Database.
  • Hi Tord, Just to let you know doing such actions in the database directly may violate the Microsoft support agreeement. There is a reason why Terminator tool force you to stop all the host instances before terminating stuff. BizTalk transactions will span across multiple databases, so without knowing the implications, I wouldn’t do any such update activitiies in the database directly.

    • Tord Glad Nordahl

      I totally agree with you Saravana!
      This may be a violation according to the Microsoft agreement! And i therefore added a “NOTE” at the top of the update script. This is mostly a script solving a small bug in BizTalk without using the terminator tool, and i do agree. Use the designated tools for this job.

      Also it does not terminate anything. It simply adds the end time to the rows.

    • Hi Saravana

      Regarding the MS Support agreement, how is running the sql update above different from running Terminator, about which MS writes:

      “Use of this tool is not supported by Microsoft, and Microsoft makes no guarantees about the suitability of this programs. Use of this program is entirely at your own risk.”

  • Andi

    Hello,
    one question regarding the “Monitor BizTalk Server” SQL Job.
    Surely you know that this is a new Job in Biztalk 2010 which monitore amongst ohter thing the orphanded messages. Now we got the error from the Job: 3 Orphaned DTA Service Instances in .BizTalkDTADb [SQLSTATE 42000] (Error 50000). The step failed.

    So what should we do in this case? is the only way to delete this 3 entries by call the terminator tool? how can we avoid this behavior in the future? thanks!

    • Tord Glad Nordahl

      I sent you a Mail buddy!

  • Xiao

    I got same error as Andi: Orphaned DTA Service Instances in .BizTalkDTADb [SQLSTATE 42000] (Error 50000). The step failed.
    Apart from using Terminator, any solution for this? We are having BTS2010 and SQL 2008 R2 SP1.

    • Tord Glad Nordahl

      I sent you an email buddy!

      • Martin

        I have the same problem. Can you send me the solution for this?

        • Tord Glad Nordahl

          Mail sent!

          • Piotr

            Hi Tord, we are experiencing the same problems with orphan DTA Service Instances.
            I would greatly appreciate if you could send me some information on how to solve this.

            Thanks!

          • Tord Glad Nordahl

            Mail sent! 🙂

          • Ahmed Samnan Raza

            Same issue, please share the solution 🙂

          • Tord Glad Nordahl

            Use the terminator tool from Microsoft! 🙂

  • Ganesh Shanmugam

    Hi Tord, I am looking for SQL query to get the number of Active instances at current time.
    I had a look on [biztalkmsgboxdb].[dbo].[instances] Table, but I see a lot of records where as I dont have any active records. Can you please assist me with a SQL query to get the active instances.

    • Ganesh Shanmugam

      I got this Query.. for getting active instances count.

      SELECT COUNT(*) as Count FROM [BizTalkMsgboxDb].dbo.[Instances]
      AS i WITH (NOLOCK) JOIN [BizTalkMgmtDb].dbo.[bts_Orchestration]
      AS o WITH (NOLOCK) ON i.uidServiceID = o.uidGUID
      where i.nstate =2

    • Tord Glad Nordahl

      Mail sent bud!

  • Bala

    Hi Tord,

    I am having same problem, can you please email me too.

    Thank you,
    Bala

  • S Gopalayer

    Hi Tord, I ran the MBV and the only critical warning we got is to do with DTA Orphaned Instances (Incompleted Instances in DTA but not in Msgbox) – The number is 7345. The situation is we can’t stop the Prod services as all our applications are on one server and there is no downtime agreed for maintenance. Can you please send me an alternate way of acheiving this as clearly this will be a problem soon. Your assistance will be much appreciated/.
    Many Thanks,
    Singa

    • Tord Glad Nordahl

      Send me a Mail and I’ll point you in the right direction

  • gopinath reddy

    Hi i Need Sql queries to get the following data from biztalk:
    • Total Jobs Processed per day.
    • Total Jobs that got suspended.
    • Total # of instances re-processed.
    • Total # of instances terminated through BizTalk along with corresponding integration.

    Please help me, as i am new to Biztalk Servers.

    Thanks,
    Gopinath

    • Tord Glad Nordahl

      You can write these queries by using the SQL queries I have on the TechNet Galleries. 🙂

    • amit

      hi gopinath,

      did you manage to get script for your query ? can you please forward me ?

      my query is deal was entered at 10:00am but it got only processed at 11:00pm so want to know were/what was deal doing for such a long duration. Appreciate your help.

      Thanks
      mit

  • Bas van W.

    Is the terminator tool not supported anymore? I’ve downloaded the latest version from the website (http://www.microsoft.com/en-us/download/details.aspx?id=2846) but when started it says that the version has expired…

    • Tord Glad Nordahl

      It should be in the folder where you installed BizTalk. I’ll look into it tomorrow and give you a shout.

  • John Inge Lestum

    Hei og takk for sist!
    As output from the monitor biztalk server sql job, we got 11712 Orphaned DTA Service Instance Exceptions. The script above does not solve this error. How do we get wrid of the Orpahaned DTA Service Instance Exceptions?

  • Johnf432

    Great, thanks for sharing this. Really Cool.

  • ingvar

    Hi Tord. Microsoft Download Center provides BTSTerminator version 2.0.0.276, which has expired. Any idea to another site to download? Thanks.

    • Tord Glad Nordahl

      They are working on a fix to this. And it should be out there. There is no other way of doing it (other then coding it yourself).. 🙂

  • nganphung

    Hi, I faced with this error. Is there anyone can help me? Thanks a lot

  • Stein Jone Lithun

    Hi Tord, thanks for sharing this. Works like magic. 😉

One Platform Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

One Platform - Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

One Platform - Operations, Monitoring and Analytics Software
ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

Back to Top