According to BizTalk best practice,we should ensure BizTalk related SQL jobs were enabled and running well in SQLagent. These jobs would help maintaining BizTalk Databases under healthystatus. However, if go to SQL Activity Monitor, the session status for job“MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb” would always be shown assuspended
This symptom is expected due to theimplementation of job “MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb”.
This job will call another storeprocedure named bts_ManageMessageRefCountLog in BizTalkMsgboxDb. There is aninfinite loop in that store procedure as illustrated below. Each time the loopbody finished, there would be a 10 seconds delay. So once the store procedureis executing delay command, the SP’s status would be set to suspended and waitfor the timer. If you pay special attention to the wait time column in ActivityMonitor, you can find the value is always less than 10000ms.
WHILE (1 = 1)
--Do the job’s work
WAITFOR DELAY '0:00:10' --delay 10 seconds
More information for Reference:
Description of the SQL Server Agentjobs in BizTalk Server