SQL Queries to Know the Instance Messages State

Introduction

In BizTalk, we are using BizTalk 360 tool from where we are using these custom queries to identify how many instances are suspended, ready to run, dehydrated, etc.

Instances State Count

Use BizTalkMsgbox;
SELECT nvcName as ApplicationName,
CASE Instances.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,
count(Instances.nState) as Count
FROM Instances
LEFT OUTER JOIN InstancesSuspended
on Instances.uidInstanceId = InstancesSuspended.uidInstanceID
LEFT OUTER JOIN [Services]
on Instances.uidServiceID = [Services].uidServiceID
LEFT OUTER JOIN Modules
on Modules.nModuleID = [Services].nModuleID
group by nvcName,Instances.nState

Instances State With Name

SELECT distinct
Modules.nvcName as ApplicationName,
CASE Instances.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,
count(Instances.nState) as Count ,
SUBSTRING(Subscription.nvcName,0,CHARINDEX(‘{‘,Subscription.nvcName,0)) as Itinerary,
Subscription.nvcApplicationName as Host
FROM Instances WITH (NOLOCK)
LEFT OUTER JOIN InstancesSuspended
on Instances.uidInstanceId = InstancesSuspended.uidInstanceID
LEFT OUTER JOIN [Services] WITH (NOLOCK)
on Instances.uidServiceID = [Services].uidServiceID
LEFT OUTER JOIN Modules WITH (NOLOCK)
on Modules.nModuleID = [Services].nModuleID
LEFT OUTER JOIN [Subscription] WITH (NOLOCK)
on [Services].uidServiceID = [Subscription].uidServiceID
where Modules.nvcName is not null
group by Modules.nvcName,Instances.nState,Subscription.nvcName,Subscription.nvcApplicationName
order by Modules.nvcName desc

Please feel free to comment if you need any further information.