Read this before using bts_CleanupMsgBox stored procedure

Subject:

Losing subscriptions after running the sproc, resulting in complete redeploy of BizTalk application(s). bts_CleanupMsgBox is not something new and it’s been there from BizTalk 2004 SP1. Basically this script is used to clear the MessageBox database (intended only for development and especially useful while during performance testing). This script is not installed by default, the stored procedure will be empty and does nothing. That’s how it should be on a production box, so no one accidentally runs it. You can install the stored procedure (please don’t do it on production server) by running the SQL script presented inside installation folder “C:Program FilesMicrosoft BizTalk Server 2006Schemamsgbox_cleanup_logic.sql” Read this great post from Lee, explaining how to use it properly. Recently, I been using the script very frequently on our performance environment after each test run to bring the environment to a clean state. Couple of days back when I ran the script, I lost all the subscriptions. We had around 13 BizTalk applications in our environment. Executing a query from Admin Console for subscription only showed subscriptions for Caching Service. Stopping and Starting the BizTalk Applications (Enlisting all Send Ports/Orchestrations) only brought back Send Port Subscriptions and Orchestrations subscriptions were not created whatsoever. The only way I managed to bring it back is by stopping/deleting existing BizTalk applications and re-importing them back in one server, then I had to apply the correct binding file and set all the modified parameters before continuing the test. We lost few hours of our valuable performance slot.

Reason:

The reason is so simple, only thing I did wrong this time was generated “EXECUTE” script from SQL Management console, which generated the script as shown below the parameter @fLeaveActSubs is by default set to “1” in the stored procedure, so if you run it normally using the command “exec bts_CleanupMsgBox”, everything is well and good, it will leave the subscriptions. But by generating the “EXECUTE” script and running it, this value is set to “0” (default), which in turn results in clearing the subscriptions.

Million Dollar Question!!

Why do we have this option in the first place in this stored procedure? Do we really need to clear the  subscriptions at any stage of testing. I can understand clearing all instance subscriptions, but why do we need to clear the activations subscriptions, and resulting in a environment where we couldn’t bring it back, unless we do a redeploy. Probably I could have tried running the piece of SQL inside the ELSE part of the IF condition @fLeaveActSubs <> 0, but I didn’t want to leave the environment in a completely unstable state. So redeployed all of our applications (time consuming but safe bet). If someone can explain the reason behind it, I’ll happily update this post. Thanks Gunjan Jain (Microsoft, India) for pointing this out. Nandri, Saravana