BizTalk SQL job “MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb” always be shown as suspended in SQL Activity Monitor
<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
NOTE: This article is migrated from Blog AsiaTech
Date: 2009-10-16 4:50 PM
<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
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
https://support.microsoft.com/kb/919776
Regards,
Bryan Yang