Entry

How do I clean the tracking database?

Apr 6th, 2005 22:22
Balaji Ramachandran, http://www.netologi.se/default.aspx?Contents=BizTalkFAQ

There is a sample SQL file to clean the tracking database. It can be 
found in %BizTalkRoot%\SDK\Samples\Admin\Database Maintenance, the name 
of the file is purge_DTADB.sql. 
After you run this SQL script against your DTA database, you need to 
call the dtasp_PruneTrackingDatabase stored procedure with a datetime 
parameter. It will delete all messages and service instances prior to 
this date time. Note that, both dtasp_CleanHmData and 
dtasp_PruneTrackingDatabase deletes the row in the tables instead of 
dropping and recreating the tables. The reason is, when the databases 
are created, there are some default information written into some 
tables and you want to preserve them.
Because of this, it may take considerable amount of time to run the 
stored procedures. If you have BizTalk NT service running, both 
applications will try to put locks on the tables, and it will take even 
more time. When you are cleaning your DTA database it is suggesed you 
stop your BizTalk NT Service which hosts tracking.