BizTalk: Undiscovered secret of DTA purge and Archive stored procedure



October 19, 2021

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))


BizTalk: New feature of DTA Purge and Archive stored procedure 1

If you replace select count (*) with select * you will be able to see that the dtEndTime is NULL as shown in the screenshot below

BizTalk New feature of DTA Purge and Archive stored procedure 2

To remove these instances from the DTA database, there are two options:

  1. Use the BizTalk Terminator tool. 
  2. 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.

BizTalk New feature of DTA Purge and Archive stored procedure 3

Steps to update the job

  1. Open SSMS (SQL Server Management Studio) and connect to the DTA database instance. 
  2. Navigate to Job Activity Manager under SQL Server agentScreenshot 2021-10-18 at 12.01.24
  3. Select the DTA Purge and Archive job and right-click on it and select Properties.
  4. In the properties window, select “Steps” from the left pane and select “Archive and Purge” and hit Edit.

    BizTalk New feature of DTA Purge and Archive stored procedure 5
  5. Edit the query in the command pane, update the last line as highlighted below, and click OK.BizTalk New feature of DTA Purge and Archive stored procedure 6

 

 

Join monthly AIMS AIOps Talk with Marius and Raman to get more tangible knowledge about implementing AIOps in your organisation

Banner Blog AIOps Talk

 

Topics from this blog: Technical

Author

Raman is an IT professional with 11+ years of proven experience in IT Project/Program Management, Service Delivery, Service Governance, Transition & Transformation management, Customer relationship management for global organizations.

Raman Dubey

Raman is an IT professional with 11+ years of proven experience in IT Project/Program Management, Service Delivery, Service Governance, Transition & Transformation management, Customer relationship management for global organizations.

More from the Author

Oct 8, 2021 2:00:51 PM
Recovery of Master Secret Server

Share this Post

Subscribe to our newsletter

RECENT ARTICLES

Technical azure aiops

Automated Azure monitoring with Artificial Intelligence

Monitoring with traditional tools and manual labor sets you up for failure. With the scope and variability of performance data that needs to be included in your monitoring scope traditional methods...
Technical

BizTalk: Undiscovered secret of DTA purge and Archive stored procedure

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...
Technical

Recovery of Master Secret Server

As a part of the recovery process during DR (Disaster Recovery) or any other unforeseen scenarios, one might have to restore the master secret to re-use the existing setup and data. This article will...