Clean up the MsgBox

Do you sometimes end up with instances in the admin console that just wont go away? That are pending to be suspended, but doesn’t seem to ever get there. I did just the other day. To solve this I used the SQL way of terminating these messages, which always seems to do the trick. But it’s not without risk. Thats one of the many reason why there is emphasis on not running this against production. As the technet how-to describes this procedure is empty by default, so remember to add the logic by running the msgbox_cleanup_logic.sql script. There is also an issue with this procedure and tracking but there is a kb hotfix available that solves that. Presumably it’s correct from the start in R2 but I haven’t compared the two.

In short the SQL way of doing it involves running the procedure bts_CleanupMsgbox (once it contains logic). This procedure has an optional parameter named @fLeaveActSubs which by default is 1. Running it with the optional parameter set to something other then 1 will cause all subscriptions to be removed. Not something you’d typically want. A special note here is that since SQL checks for <> 0, passing in null will cause it to behave as if you sent in 0, since null compared to anything in SQL is always false. This is an issue because if you do Script As Execute in SQL the code that gets generated will default to you passing in null:

DECLARE @RC int

DECLARE @fLeaveActSubs int

— TODO: Set parameter values here.

EXECUTE @RC = [BizTalkMsgBoxDb].[dbo].[bts_CleanupMsgbox] @fLeaveActSubs

Your ports and orchestrations will still look like their running, and in a way they are, but there are no subscriptions, so you’re likely to get a routing failure. For you to get your subscriptions back you need to unenlist the send ports and then restart them. For orchestrations this isn’t enough, here you have to go the additional length of un-binding them and then re-configure and start them.

The BizTalk Core Engine’s WebLog mentions in this post that it might also be a good idea to run bts_PurgeSubscriptions directly after, but if your SQL Agent and the PurgeSubscriptionsJob is enabled (and it should be) then this will be done as part of that job.