MsgBox Cleanup - SQL way
From Balaji's BizTalk Bits:
In the <install dir>\schema directory you will find a file called msgbox_cleanup_logic.sql (it is installed with SP1). This file contains the definition for a stored procedure called bts_CleanupMsgbox. By default, this stored procedure exists in your msgbox except that the default implementation is empty so it does nothing. To use this script first run the .sql file against all msgboxes using Query Analyzer. This will simply create the stored procedure. It won't actually do anything to your box as far as cleanup goes
Then follow the steps below.
- Stop BTS.
- Do IISRESET to recycle your IIS.
- Run "exec bts_CleanupMsgbox" on your message box database. This will mark your instances for deletion and it will not actually delete it.
- Run "exec bts_PurgeSubscriptions" on your message box database. This will purge all your messages marked for deletion.
- Restart your BTS.
Note: Never run this script on your production database. This is only for Test Environment.