Archive

Archive for August, 2008

DTA Orphaned Instances

August 5, 2008 4 comments

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