View Message Count by BizTalk Orchistration

This script will give you a count of all messages by orchestration within BizTalk. This includes currently processing messages.

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

SELECT  
	o.nvcName AS Orchestration, 
	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 dtCreated > '2004-08-24 00:00:00' AND dtCreated < '2004-08-24 13:30:00'  
GROUP BY o.nvcName, i.nState