Large BizTalkMsgBoxDb and Performance

I came across another classic BizTalk Administration mistake today.  A client called concerned about a very poor performing BizTalk Server environment.  It’s a single server environment processing up to 200,000 individual ochestration instances per day.  It’s safe to say the traffic between the BizTalk and SQL Server is somewhat higher.
First thing we spotted was the data file for the BizTalkMessageBoxDb was out to over 35GB.  This is a classic cause of poor BizTalk performance. After confirming the SQL Agent was running we took a closer look at the BizTalk jobs.  It appeared someone had disabled "MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb".  This job calls the "MessageBox_Message_Cleanup_BizTalkMsgBoxDb" job which cleans the message box.  In laymans terms messages are published into the message box then acted upon by subscribers.  Once subscribers are done with the messages they are flagged for deletion.  If the two jobs mentioned are not being run then the message box will grow until it runs out of disk space.
It is important to note the "MessageBox_Message_Cleanup_BizTalkMsgBoxDb" is and should be disabled.  This job should not be run manually.  It is also worth mentioning the reason why the "MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb" job had been disabled.  The client had noticed the job was scheduled to run from 20:00 to 2:59 but the job permanently stayed in the running state.  Thinking it was a rouge job they killed it.  Note this behaviour is by design.  When the job is started logic is encountered down in the stored procedures that puts the job into an infinite loop.  There is litterally a while (1 = 1) in the stored procedure.
We re-enabled "MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb" and the BizTalkMessageBoxDb started decreasing albeit at a very slow rate.  Further action may still need to be taken to quickly truncate the message box to restore operations. 
Technorati Tags:
This entry was posted in BizTalk Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s