DTA Database is one of the most important databases in the BizTalk DB component. The issue in this database can cause a lot of performance issues in BizTalk. To keep the DB healthy, Microsoft provided some inbuilt store procedures in BizTalk, dtasp_BackupAndPurgeTrackingDatabase is one such stored procedure.
This blog is not for repeating the usage of the dtasp_BackupAndPurgeTrackingDatabase but to highlight the new feature of the stored procedure. Before revealing the new functionality of the stored procedure, we would like to highlight one of the bugs in the BizTalk which is in the system for quite some time: the orphaned DTA instances.
While tracking, BizTalk creates a new row in the dta_ServiceInstance table. The cleanup jobs remove the row from the table but due to the bug in BizTalk, some of the rows stay in the table creating the orphans. This is because when tracking starts the field dtStartTime is set to the current UTC time, similarly dtEndTime is set when the tracking ends. Sometimes due to high exception or use of request-response for the Sendport group the dtEndTime doesn’t set up. The cleanup jobs only remove the rows with complete data and incomplete rows remain in the system causing orphaned instances.
A high number of orphaned instances in DTA Database, but obviously, creates performance issues in the DB and in turn the whole BizTalk environment. If you want to check the number of orphaned instances in your database, please run the below 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))
If you replace select count (*) with select * you will be able to see that the dtEndTime is NULL as shown in the screenshot below
To remove these instances from the DTA database, there are two options:
- Use the BizTalk Terminator tool.
- Update the dtasp_BackupAndPurgeTackingDatabase.
To use the terminator tool needs some downtime in the environment as we must stop BizTalk host instances. The second approach is using the dtasp_BackupAndPurgeTackingDatabase and it will take care of the orphaned DTA instances.
The stored proc can now be updated with an extra argument and as the job runs every minute, we can get rid of the orphaned instances.
Adding the below highlighted (in red) argument can remove the orphaned instances.
Steps to update the job
- Open SSMS (SQL Server Management Studio) and connect to the DTA database instance.
- Navigate to Job Activity Manager under SQL Server agent
- Select the DTA Purge and Archive job and right-click on it and select Properties.
- In the properties window, select “Steps” from the left pane and select “Archive and Purge” and hit Edit.
- Edit the query in the command pane, update the last line as highlighted below, and click OK.
Join monthly AIMS AIOps Talk with Marius and Raman to get more tangible knowledge about implementing AIOps in your organisation
Topics from this blog: Technical
