Freigeben über

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

  • Artikel

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