How to detect BizTalk is not processing new requests for a while

  • Article

With this post i will start writing about a set of  T-SQL queries that can be used to monitor the BizTalk health.

While there are several performance counters that can be used to detect BizTalk it is not processing new request, with the following simple query administrators can easily find out if something is wrong under the hood (if tracking is used)

 

USE BizTalkDtaDb

DECLARE @TimeThreshold as int

SET @TimeThreshold =5 --minutes

SELECT TOP 1

datediff(minute,[ServiceInstance/StartTime],getutcdate()) as DiffTime,

[ServiceInstance/State],

(case when datediff(minute,[ServiceInstance/StartTime],getutcdate())>= @TimeThreshold then 'Something is wrong (Too much time since last request)' ELSE 'Everything Ok (Less than ' + cast(@TimeThreshold as varchar(3)) + ' minutes since last request)' end ) as Biztalk_Status,

@TimeThreshold as UsedTimeThreshold

FROM dbo.dtav_ServiceFacts sf WITH (READPAST) ORDER BY sf.[ServiceInstance/starttime] DESC

 

How To use it

Well, if you take a look at the query it starts by using the BizTalkDtaDb which means this procedure ONLY WORKS for environments with some tracking activated (it does not matters the level used )

This is the output:

image

The variable @TimeThreshold  it is used to define the time (in this case in minutes)  the query will alert in case BizTalk it is not processing nothing.  In this case five minutes it is considered a bad thing.

image

The output Column DiffTime will show the elapsed time between the last item processed by BizTalk and the current time. Be aware of everything in DTADb it is saved using UTC time, this is the reason to use the function getutcdate() to get the current time. If minutes it is not enough for you, it is possible to use seconds or milliseconds by changing the datediff call:

datediff(seconds,[ServiceInstance/StartTime],getutcdate()) as DiffTime

KeyNotes:

  • The query will work from The SQL Enterprise Management Studio and from HAT
  • You need tracking enabled
  • Could be used to identify problems on tracking or (if tracking working, BizTalk it is not processing new requests)
  • You can extend the query to check just  the last processed element state is “started” or “Ready To Run” or “active”