Hidden gem in SP1 ... cleaning up the msgbox in a test environment

  • [アーティクル]

So now that Jean is posting, this blog should get pretty interesting. Jean is the man, behind the man, behind the man. :) On the current BizTalk team he is about employee number 5 or so which makes him a true wealth of knowledge and I have been working with him for 5 1/2 years and he hasn't killed me yet so he must have a lot of patience too. :) Good to have him on board. :) :)

I have gotten many questions over time from people asking how to cleanup their messagebox when they are running in a *test* environment without having to reconfigure all of the databases. Sometimes you run a test and something fails and you get 1000s of suspended messages in the database that you don't know what to do with (and HAT takes to long to terminate ... which we are trying to fix). Well, we have had an internal tool for quite a while for doing this which I was finally given permission to include in SP1, but we hid it so here is my chance to let you know that it exists and how to use it (there is a KB article on it, but I haven't found it ... haven't looked too hard :) ).

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:

1) Shut down all bts servers

2) If you are using HTTP or SOAP run IISRESET from the cmd prompt to recycle IIS and shutdown our out-or-process host instance

3) If you have any custom Isolated Adapters, make sure they are shutdown also.

4) run "exec bts_CleanupMsgbox" on all of your msgboxes

5) Restart everything and away you go

What will this do???

You can certainly read the stored procedure to see what we are doing (since you guys seem to read all the other ones I write :), but the summary is that this deletes all running instances and all information about those instances including state, messages, and subscriptions.It leaves all activation subscriptions so that you do not have to reenlist your orchestrations or sendports. Everything will just work and now you no longer have 50,000 instances sitting in a suspended state.

Couple of notes / gotchas:

1) If you install a hotfix onto your test system which runs msgboxlogic.sql, it will overwrite this with the empty stored procedure, so you will have to recreate it by rerunning the .sql file.

2) If you create a new msgbox it will by empty on the new msgbox and you will have to run it there also

3) THIS IS NOT SUPPORTED ON PRODUCTION SYSTEMS!!!! I have no idea why you would ever use this on a production box since this will delete all of your data but do not do it. Do not even run the cleanup_logic.sql file on your production system. Just don't even think about it cause your data will disappear and I can't imagine that you would be happy.

4) READ NUMBER 3. :) :)

5) This script does not actually delete all of the subscriptions. It marks them for deletion and then allows the subscripton cleanup job run by sql agent to take care of them. They will not be filled by the routing process since they have been "ghosted", but if you have 100's of thousands of subscriptons, this could be a little bit of overhead. Right now, for those situations, you just have to wait for the job to finish. Sorry. This is pretty rare scenario that has this happen. To make sure you are okay, after running the script, go to the management node in enterprise manager for your server and under jobs for sql agent, kick off the PurgeSubscriptionsJob_<msgboxname> job and wait for it to finish. Once it finishes, you are golden. This is very rare, but if you are worried about it, do this step to feel okay.

Besides that, you are golden. This is a very usefull script when rerunning tests after fixing an issue in your configuration. Do not abuse this script though and use it as a crutch for not fixing stress issues in your scenario. If you have issues which are causing the msgbox to overload and become unstable, they should be investigated because you do not want them to occur in your production system. Keep that in mind. Hopefully this script will make it easier for you to run tests efficiently.

 

Thx

Lee

Comments

  • Anonymous
    August 21, 2006

    DISCLAIMER --The bts_CleanupMsgbox stored procedure IS NOT SUPPORTED. IT IS NOT TO BE USED ON PRODUCTION...
  • Anonymous
    September 01, 2006
    PingBack from https://blogs.msdn.com:443/tihot/archive/2006/08/21/711340.aspx
  • Anonymous
    December 13, 2007
    Do you sometimes end up with instances in the admin console that just wont go away? That are pending
  • Anonymous
    July 19, 2008
    Read this before using bts_CleanupMsgBox stored procedure.
  • Anonymous
    July 29, 2008
    Read this before using bts_CleanupMsgBox stored procedure.
  • Anonymous
    May 31, 2009
    PingBack from http://outdoorceilingfansite.info/story.php?id=5129