Cleaning up Biztalk tracking database

Tracking may tend to grow fast, depending on your how intensive your needs are. Out of the box, BizTalk Server does not offer any tools to "autoclean" the tracking database. However, there's a sample in the BizTalk Server SDK directory exactly targetting the subject. Although it's just a sample, I've never seen this fail and it seems to me that the given SQL script is really high quality coded!!

To cleanup the tracking database:
If you've never done this procedure before, first do this:

1. Open up SQL Server SQL Query Analyzer
2. Connect to the BizTalkDTADb database
3. "File - Open", and go to the BizTalk Server SDK directory and look for the Database Maintenance subdirectory
4. Select the "Purge_DTADB.sql" SQL script file
5. Hit F5
6. You should see a message "Creating stored procedure dtasp_PruneTrackingDatabase", in addition the status bar below should mark the operation as: "Query batch completed".You have just created a new stored procedure called "dtasp_PruneTrackingDatabase". This procedure allows you to prune the tracking database. It takes only one single parameter: @PruneBeforeDate. Executing this SP causes the tracking data to be purged that was older then the given (@PruneBeforeDate) date. So, let's try this.
7. To execute the procedure:
   Select: "File - New - Blank Query Window"
   Type following statements in order to cleanup *all* tracking data before today:
   DECLARE @Today datetimeSET @Today = GETDATE()EXEC dtasp_PruneTrackingDatabase @Today

Depending on how much data is to be purged, after a while the Stored Procedure will notify you with its results. Possibly you may see a message in red, commented as: "Duplicate key was ignored." This is fine and is an expected result.

To verify this:

1. Open HAT
2. Select: "Reporting - Find Message"
3. Try selecting a schema
4. If you've cleaned everything, you should not see any schemas anymore... (Only schemas here should correspond to messages that were tracked, áfter the @PruneBeforeDate date parameter.)

If Tracking database is too huge(> 3 GB), it might be better to truncate the complete content in the tables, rather than trying to clean up one row at a time.