Out of the box, BizTalk Server 2006 doesn't provide any tool for auto clearing and cleaning up database used by BizTalk, this cause problems like performance and data storage issues. While browsing BizTalk installation directory (\BizTalk Installation Folder\Schema) I've found out few sql scripts that can used to cleanup the MessageBox = BizTalkMsgBoxDb and Tracking = BizTalkDTADb database.
To clean MessageBox:
1) Stop all BizTalk related services.
2) Open Analyzer and open sql script: msgbox_cleanup_logic.sql, press F5 (this will create the sp) - be sure to use BizTalkMsgBoxDb.
3) run bts_CleanupMsgbox
4) Start services.
1) Execute dtasp_CleanHMData sp from BizTalkDTADb
There are many sql script located in that Schema Directory and by reading and analyzing this scripts it will give us better understanding on how BizTalk stores the data.
|re: BizTalk Database Maintenance
I think it is very important to point out that the comments you make above are only for development machines and should not be done in a production environment.
BizTalk has a number of SQL jobs which manage clearing of old messages etc and archiving/purging tracking data. These need to be configured and ran as per the biztalk documentation
The above will clean the databases alright but will also remove any inflight messages too