BizTalk MessageBox query

  • Article

To get all the Suspended Resumable or Suspended non-resumable orchestration from the MessageBox, use the following query:

-- All Errors group by errors by instances

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW

select o.nvcName,
i.nvcErrorDescription,
COUNT(*) as Count,
CASE i.nState
WHEN 1 THEN 'Ready To Run'
WHEN 2 THEN 'Active'
WHEN 4 THEN 'Suspended Resumable'
WHEN 8 THEN 'Dehydrated'
WHEN 16 THEN 'Completed With Discarded Messages'
WHEN 32 THEN 'Suspended Non-Resumable'
END as State

FROM[BizTalkMsgboxDb]..[Instances] AS i WITH (NOLOCK)
JOIN [BizTalkMgmtDb]..[bts_Orchestration] AS o WITH (NOLOCK) ON i.uidServiceID = o.uidGUID
WHERE i.nState in (4,32)
GROUP BY o.nvcName,i.nvcErrorDescription, i.nState

Change the i.nState comparison value to get the results for other status like Active or Ready to run.