Read this before using bts_CleanupMsgBox stored procedure

|  Posted: June 4, 2008  |  Categories: General Technical
Tags: General SQL

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

sql script in management console

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

Author: Saravana Kumar

Saravana Kumar is the Founder and CTO of BizTalk360, an enterprise software that acts as an all-in-one solution for better administration, operation, support and monitoring of Microsoft BizTalk Server environments.

  • Maganista

    Hello,

    I would suggest a response to your question 🙂
    If you have the possibility to terminate all instances ( running ones and suspended ), also the possibility to stop all applications or we can make a script if we have a lot of applications. Why we will need such stored procedure ?

    So i think the stored procedure should be used in hard situations when Biztalk administration console is not responding or any other problems. Then they put all options in case of there’s any problems we can use the SP.

    But i think that the subscription option should have another number instead of 0. Because like you mentioned it ” it’s very dangerous and useless in most cases “.

    Thank’s for this great article 🙂

One Platform Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

One Platform - Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

One Platform - Operations, Monitoring and Analytics Software
ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

Back to Top