Share via

Microsoft BizTalk: Health Check by SQL Query

  • Article

Issue:
How to check Microsoft BizTalk suspended messages by SQL Query.

**Reason: **
By some reason developer or any other managerial person have not access to login to BizTalk server.

**Solution:
**A SQL query to get suspended messages (Resumable, non-Resumable) with other helpful details and email to concern persons by SQL scheduled job.
Developer should have access on BizTalk databases like "BizTalkMsgBoxDb", "BizTalkDTADb" and "BizTalkMgmtDb" etc.

SELECT (
CASE 
WHEN _DTS.strServiceName IS NOT NULL
THEN _DTS.strServiceName + ', ' + _DTS.strAssemblyName
WHEN _BSP.nvcName IS NOT NULL
THEN _BSP.nvcName
WHEN _BRP.nvcName IS NOT NULL
THEN _BRP.nvcName
ELSE 'Unknown'
END
) AS [Serivce Name]
,_MOD.nvcName AS [ApplicationName]
,ISNULL(_SC.nvcName,'Routing Failure Report') AS [Service Class]
,(
CASE _INS.nState
WHEN 4
THEN 'Suspended (Resumable)'
WHEN 32
THEN 'Suspended (Non-Resumable)'
ELSE 'Unknown'
END
) AS [Status]
,DATEADD(hh, - 8, _INS.dtSuspendTimeStamp) AS [Creation Time]
,_INS.nvcErrorID AS [Error Code]
,_INS.nvcErrorDescription AS [ErrorDescription]
FROM BizTalkMsgBoxDb.dbo.[InstancesSuspended] _INS WITH (NOLOCK)
LEFT JOIN [BizTalkMsgBoxDb].dbo.[Services] _SRV WITH (NOLOCK) ON _INS.uidServiceID = _SRV.uidServiceID
LEFT JOIN [BizTalkMsgBoxDb].dbo.[Modules] _MOD WITH (NOLOCK) ON _MOD.nModuleID = _SRV.nModuleID
LEFT JOIN [BizTalkMsgBoxDb].[dbo].[ServiceClasses] _SC WITH (NOLOCK) ON _INS.uidClassID = _SC.uidServiceClassID
LEFT JOIN [BizTalkMsgBoxDb].[dbo].[Subscription] _SUB WITH (NOLOCK) ON _INS.uidInstanceID = _SUB.uidInstanceID
LEFT JOIN [BizTalkDTADb].dbo.[dta_Services] _DTS WITH (NOLOCK) ON _INS.uidServiceID = _DTS.uidServiceId
LEFT JOIN [BizTalkMgmtDb].[dbo].[bts_sendport] AS _BSP WITH (NOLOCK) ON _INS.uidServiceID = _BSP.uidGUID
LEFT JOIN [BizTalkMgmtDb].[dbo].[bts_receiveport] AS _BRP WITH (NOLOCK) ON _INS.uidServiceID = _BRP.uidGUID