0

SQL Script for suspended message detail in BizTalk

Posted by Chris Straw on November 27, 2011 in BizTalk, SQL Server |

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

Tags: ,

Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright © 2011-2019 Chris Straw All rights reserved.
This site is using the Desk Mess Mirrored theme, v2.5, from BuyNowShop.com.