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

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

Regards,

Bryan Yang