Since I always have a hard time remembering where it is and what to run, there are two scripts that you can run in your non-production region that can clean up currently running orchestrations that you have already terminated and it is waiting to clear it up.
You will first need to go and create the real stored procedure bts_CleanupMsgBox (this replaces the shell that is there). This file is located in the ..\BizTalk Server 200X\Schema\msgbox_cleanup_logic.sql. (I know that this was an add-on in 2004 SP1, but the stored proc needs to be loaded within 2006 also)
There is a new stored proc that can be obtained by calling support and referencing this article.
The instructions are as follows to clean it up:
- Stop BTS.
- Do IISRESET if you are using web services.
- Run “bts_CleanupMsgbox” on your message box database. This will mark your instances for deletion and it will not actually delete it.
- Run “bts_PurgeSubscriptions” on your message box database. This will purge all your messages marked for deletion.
- Restart your BTS.
If you want to completely purge the DTADb there is a nice little stored proc that takes it time to run, but it gives you a completely clean database:
- Stop BTS
- Run “dtasp_CleanHMData” on the DTADb
- Restart your BTS