In BizTalk 2000 and 2002, document tracking can be a right royal pain in the bottom if you (or a predecessor) leave it on by mistake.
In one particular example, Document Tracking was left on and database InterchangeDTA mdf file filled the disk up (22gb) when client put through large # of XMLs. As the data was not required we detached database, deleted db and recreated db using a generated script from an identical version/type of BizTalk (details below). 300k suspended messages were also removed.
Quick, Cheap and Dirty Resolution:
First of all confirm with client you need document tracking on. If not turn it off. It makes life easier.
If you
do need to keep it on then you need to design a maintenance plan using the commands and purge stored procedure described below in a sql job.
Assuming now you haven't maintained tracking and your disk is full, if you need to retain the data then you will need to lookup the sql commands
BACKUP LOG XXX,
SHRINKFILE XXX,
SHRINKDATABASE XXX and install the stored procedure '
dta_purge_old_records' (found in
Microsoft BizTalk Server/Setup/) to reduce the size. Research to find how to use. You can specify cut-off point in hours e.g. 24 will purge every thing apart from the last days tracking.
If you do not mind losing all the tracking data then you can totally replace this db by:
- Detaching InterchangeDTA
- Creating a generate db script of InterchangeDTA from another machine
CAUTION: Ensure you find out whether the version you are working with is a Partner edition or Enterprise edition and create a script with the same type). If you do not you will get group related errors and BT messaging service will not start!
- Run the create db script in master (ensuring you update folders at top of script).
- Ensure that the relevant users have read/write permissions on this new database (otherwise you may have problems starting the BizTalk messaging service).
The 300k suspended messages were removed by running the command
DELETE FROM cs_SuspendedQ in the
InterchangeSQ database. This did not seem to have any side-effects (thank goodness).