Home > BizTalk Server > DTA Orphaned Instances

DTA Orphaned Instances

BizTalk Server 2006 (including R2 apparently – see warning section of this page) seems to have a bug, for which I’ve seen no fix, that affects the performance and size of the DTA (BizTalkDTADb) database because it fills it up with orphaned instances.  You can detect these using the following query:

select count(*) from [dbo].[dta_ServiceInstances] where dtEndTime is NULL and [uidServiceInstanceId] NOT IN ( SELECT [uidInstanceID] FROM [MSGBOXSERVER].[BIZTALKMSGBOXDB].[dbo].[Instances]
UNION
SELECT [StreamID] FROM [MSGBOXSERVER].[BIZTALKMSGBOXDB].[dbo].[TrackingData]
)

These can also be detected by the MsgBoxViewer, a great tool that I’d recommend for all BizTalk administrators.

As you may be able to see from the query above, an orphaned instance is one that never finishes.  This can happen for a few, very common, reasons.  For example, an orchestration might throw an exception, or might be terminated by an administrator.  It seems silly to me that these stay in your DTA database forever, but nonetheless, they do.

So how do you fix this?  You can run this update command:

UPDATE [dbo].[dta_ServiceInstances] SET [dtEndTime] = GetUTCDate() where dtEndTime is NULL and [uidServiceInstanceId] NOT IN ( SELECT [uidInstanceID] FROM [MSGBOXSERVER].[BIZTALKMSGBOXDB].[dbo].[Instances]
UNION
SELECT [StreamID] FROM [MSGBOXSERVER].[BIZTALKMSGBOXDB].[dbo].[TrackingData]
)

Here I had set [dtEndTime] = GetUTCDate() but you might want to change this after taking into consideration your “soft delete” date specified in your DTA purge job.  If you have a soft delete date of 14 days, for example,  you might want to set this to currentutcdate()-14 so that the next time the DTA purge and archive runs it will clear out these instances.

Advertisements
Categories: BizTalk Server
  1. August 8, 2008 at 12:37 am

    Thanks Victor, I never knew that, and when checking our environment we have found a few “orphaned” instances.
    The scope of the problem seems very small, at least on our production environment, never-the-less we now have a job to clear those outside the hard delete time window of the scheduled purge job.
    We don’t have long-running processes, so this is safe to do. this would be a bigger problem for anyone who does as it’s hard to tell which instances can safetly be removed.

  2. August 8, 2008 at 9:58 am

    Great point Yossi regarding long-running orchestrations. Presumably this is why BizTalk doesn’t get rid of these. It would seem there should be some other way for BizTalk to differentiate these (or if not, that the design should change). Thanks for the comment.

  3. raul caceres
    November 28, 2008 at 11:38 am

    hi
    my problem is
    im have orchestration in state active that wait for be comsumed int el biztalkMsgboxDB , i reset the host instance y nothing.
    its same orhcestration are in the BIZTALKDTAdb In state STARTED , this have not a dtEndTime and DtStartTime , some will can say how complet this instances.
    thank you

  4. December 2, 2008 at 4:06 pm

    Raul,

    I think I need a little more background to understand your situation. Can you briefly describe your orchestration? I’m not sure what you mean by it waiting to be consumed… Why is it not ending on its own? I need you to clarify a bit to figure out what’s going on.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: