Large BizTalkDTADb Tracking Database: What to do

  • Article

On a discussion alias, this question was asked:

The DTA Purge & Archive job has not been executed in 2 years of operation. The DTADb database is about 60 GB. Should we recommend any procedures to do before running the job. Any modifications to the job or stored procedures to help ease this job into a regular schedule? Any idea of the resource impact of running it for the first time in 2 years on a 60 GB sized database.

The BizTalk DTA Tracking database stores tracked data that has already been processed through the MsgBox database. BizTalk does not use the tracked data in the DTA Tracking database. As a result, the data is yours to do with as you please.

When the DTA Tracking database gets too large, performance is impacted. There are different ways to approach this scenario so do what's right for you. Personally, I always get a MsgBox Viewer report before doing anything. This way, I can see the problematic tables, check the status of the SQL Agent jobs and check for other potential issues.

 

To answer these questions:

 

Question: Should we recommend any procedures to do before running the job?

Answer: Absolutely. Options include:

 

Option: Keep the Data

In this scenario, you want to keep the tracked events for troubleshooting, historical research, whatever the reason. This option involves backing up the database and restoring to an alternate location. Steps include:

1. Stop all host instances, stop the IIS services and stop SQL Server Agent.

2. Backup the DTA tracking database (by default, this is the BizTalkDTADb database) using the SQL Server Backup methods. The SQL Server Backup method can be used because BizTalk is stopped.

How to: Back Up a Database (SQL Server Management Studio)
https://msdn.microsoft.com/library/ms187510(v=sql.105).aspx

3. Recover the newly-backed up databases to an alternate location. In the BizTalk Administration console, you can connect to a backup Tracking database. 

4. Download the BizTalk Terminator tool https://www.microsoft.com/download/details.aspx?id=2846. Then, determine what you want to purge:

-- Purge everything: Execute the PURGE Everything in the DTA 2006 or Above script. This is the quickest way to start with a clean database.

-- Purge some tables. Run MsgBox Viewer to see the largest tables. The following tables are typically the largest:

  • TrackingData_0_x & TrackingData_1_x tables: In Terminator, execute the PURGE TrackingData Tables in MsgBox script.
  • dta_DebugTrace: In a SQL Query, execute: TRUNCATE TABLE BizTalkDTADb.dbo.dta_DebugTrace
  • dta_MessageInOutEvents: In a SQL Query, execute: TRUNCATE TABLE BizTalkDTADb.dbo.dta_MessageInOutEvents

 

Option: Purge the Data

In this scenario, you don't want or need the tracked data. Steps include:

1. Stop all host instances, stop the IIS services and stop SQL Server Agent.

2. Download the BizTalk Terminator tool https://www.microsoft.com/download/details.aspx?id=2846. Then, determine what you want to purge:

-- Purge everything: Execute the PURGE Everything in the DTA 2006 or Above script. This is the quickest way to start with a clean database.

-- Purge some tables. Run MsgBox Viewer to see the largest tables. The following tables are typically the largest:

  • TrackingData_0_x & TrackingData_1_x tables: In Terminator, execute the PURGE TrackingData Tables in MsgBox script.
  • dta_DebugTrace: In a SQL Query, execute: TRUNCATE TABLE BizTalkDTADb.dbo.dta_DebugTrace
  • dta_MessageInOutEvents: In a SQL Query, execute: TRUNCATE TABLE BizTalkDTADb.dbo.dta_MessageInOutEvents

 

Question: Any modifications to the job or stored procedures to help ease this job into a regular schedule?

Answer: After the database has been cleaned up, then configure the job. You can be aggressive with the purging options or not so aggressive. It depends on how long and how much data you want stored in the DTA Tracking database. How to Configure the DTA Purge and Archive Job describes the job parameters.

 

Question: Any idea of the resource impact of running it for the first time in 2 years on a 60 GB sized database?

Answer: Executing the DTA Purge and Archive job with 60 GB worth of data is going to take a very long time, possibly days. As a result, don't do this. Configure the job after the database has been cleaned up.

 

Additional Tasks

  

Biztalk Terminator
https://www.microsoft.com/download/details.aspx?id=2846

MsgBox viewer
https://blogs.technet.com/b/jpierauc/archive/2007/12/18/msgboxviewer.aspx

952222  How to maintain and troubleshoot BizTalk Server databases
https://support.microsoft.com/kb/952555

How to: Back Up a Database (SQL Server Management Studio)
https://msdn.microsoft.com/library/ms187510(v=sql.105).aspx