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