SQL Script for suspended message detail in BizTalk

This Script will give you detailed information on suspended message in BizTalk. Run this script against the BizTalkMsgBoxDb.

set nocount on
set transaction isolation level read COMMITTED
set deadlock_priority LOW

SELECT
      nvcName as ApplicationName,
      nErrorCategory,
      DATEADD(hh,-5,dtSuspendTimeStamp) as DateSuspended, -- Subtract the appropriate hours for your timezone
      nvcAdapter as Adapter,
      nvcURI as URI,
      nvcErrorDescription as ErrorDescription,
	CASE 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 InstancesSuspended WITH (NOLOCK)
LEFT JOIN [Services] WITH (NOLOCK)
      on InstancesSuspended.uidServiceID = [Services].uidServiceID
LEFT JOIN Modules WITH (NOLOCK)
      on Modules.nModuleID = [Services].nModuleID
ORDER BY dtCreated DESC