FIX: BizTalk Server Does Not Purge or Archive Tracking Database

A default installation of BizTalk Server is configured to automatically perform Document Tracking to store metadata that is associated with an interchange into the BizTalk Server tracking database (InterchangeDTA). This default behavior can be problematic because the BizTalk Server tracking database continues to grow in size and consumes resources indefinitely.

Microsoft does not recommend manually deleting records in the database because the schema is complex and there is a risk of deleting useful live information about your business processes that are running.

BizTalk Server has no built-in facility for purging and archiving the InterchangeDTA tracking database.

To resolve this problem, obtain the latest service pack for Microsoft BizTalk Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: To resolve this problem, install SQL Server Database stored procedures that will correctly archive and purge the database of expired schedule instances. Schedule these cleanup procedures to run periodically by using SQL Server jobs.

The following SQL Server scripts are available for archiving and purging the BizTalk Server tracking database:

• BTS_Tracking_ArchiveDB_Schema.sql: This script creates the Archive Database schema for the BizTalk Tracking database.
• BTS_Tracking_Archive_Purge_Script.sql: This script creates a stored procedure (dta_purge_old_records) that is designed to query for old interchanges in the BizTalk Tracking database, and then purge and optionally archive all related records. A SQL Server Agent job is also created to invoke this stored procedure on a regularly scheduled basis. To fix a deadlock issue triggered by the introduction of this stored procedure, there are three existing logging stored procedures used by the runtime that are modified by this script.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft BizTalk Server 2000 Service Pack 1.

In addition to periodically purging and archiving your tracking database, Microsoft recommends that you manage the size of your tracking database by controlling what you log.

You can adjust the level of logging by doing the following:

• Change the Document Logging settings in the BizTalk Messaging Manager Channel Properties:
  
  
  1. Start the BizTalk Messaging Manager.
  2. Under Search for other items: in the left-hand pane of the Messaging Manager, click Channels, and then click Search Now.
  3. In the right-hand pane of the Messaging Manager, double-click the channel for which you want to configure logging.
  4. Click Next twice to open the Inbound Document page of the Channel Properties.
  5. Set the tracking options in the Tracking for Inbound Document dialog box.
• Change the Tracking settings for the BizTalk Server Group Properties in the BizTalk Server Administration:
  
  
  • In the BizTalk Server 2000 Microsoft Management Console (MMC); right-click the BizTalk Server Group, and then click Properties.
  • Click the Tracking tab.
  • Select the appropriate level of document tracking.