I was just reading Mike Holdorf's post on the bts_CleanupMsgbox stored procedure. The team I work with has been able to make good use of this sproc as well, especially while stress testing.
One thing to beware of is this: Your habit for doing this kind of thing might lead you to open up Sql Query Analyzer, and right click on the stored procedure - selecting “script to new window as execute.” This will generate the following:
DECLARE @RC int DECLARE @fLeaveActSubs int -- Set parameter values EXEC @RC = [BizTalkMsgBoxDb].[dbo].[bts_CleanupMsgbox] @fLeaveActSubs
Ahhh, but the stored procedure actually defaults fLeaveActSubs to “1”, whereas this code will leave fLeaveActSubs as “0”. This will cause all your subscriptions to be deleted, and you will have to redeploy your BizTalk applications. Not that I would know....
So just do “exec bts_CleanupMsgbox”...