Nov 142006

Since I always have a hard time remembering where it is and what to run, adiposity 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 200XSchemamsgbox_cleanup_logic.sql. (I know that this was an add-on in 2004 SP1, dosage 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:

  1. Stop BTS.
  2. Do IISRESET if you are using web services.
  3. Run “bts_CleanupMsgbox” on your message box database. This will mark your instances for deletion and it will not actually delete it.
  4. Run “bts_PurgeSubscriptions” on your message box database. This will purge all your messages marked for deletion.
  5. 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:

  1. Stop BTS
  2. Run “dtasp_CleanHMData” on the DTADb
  3. Restart your BTS
  • prasoon

    hi Eric,
    i found your blog very usefull to me but when try to search the “\BizTalk Server 200X\Schema\msgbox_cleanup_logic.sql.” file it was not there in the folder, i am using BT 2004. Anyhow, i found the bts_CleanupMsgBox stored procedure in msgboxlogic.sql file but no logic is given in that it is a blank stored procedure given below:
    CREATE PROCEDURE [dbo].[bts_CleanupMsgbox]
    @fLeaveActSubs int = 1


    will i have to write the logic in that if yes please give the logic too.
    please advice me ASAP….

  • If memory serves me correct, this is installed as part of BTS SP1.

  • Prasoon

    BTS SP1 is already installed on my system but i am still not getting the sp. but SP-2 is not installed.
    shloud i install the BTS SP-1 again?

  • If you go into the schema directory in BizTalk, you need to install it first from there, and then execute it. I don’t think that I mentioned that.

  • Dan

    Thanks for that tip, Eric! It worked great – I had some “active” isolated host instances that would not go away no matter what. This cleared it up.

  • Mike

    Thanks _so__ much, that worked perfectly.

  • Roland

    Eric for president!
    (at leat in BizTalk country)

  • ghai dialect pronunciation spelling of the end object of the