BizTalk SQL job “MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb” always be shown as suspended in SQL Activity Monitor


 


Symptom:


According to BizTalk best practice, we should ensure BizTalk related SQL jobs were enabled and running well in SQL agent. These jobs would help maintaining BizTalk Databases under healthy status. However, if go to SQL Activity Monitor, the session status for job “MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb” would always be shown as suspended


 


Explanation:


This symptom is expected due to the implementation of job “MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb”.


This job will call another store procedure named bts_ManageMessageRefCountLog in BizTalkMsgboxDb. There is an infinite loop in that store procedure as illustrated below. Each time the loop body finished, there would be a 10 seconds delay. So once the store procedure is executing delay command, the SP’s status would be set to suspended and wait for the timer. If you pay special attention to the wait time column in Activity Monitor, you can find the value is always less than 10000ms.


……


WHILE (1 = 1)


BEGIN


–Do the job’s work     


      WAITFOR DELAY ‘0:00:10’ –delay 10 seconds


END


……


 


More information for Reference:


Description of the SQL Server Agent jobs in BizTalk Server


http://support.microsoft.com/kb/919776


Regards,


Bryan Yang


 

Comments (0)

Skip to main content