BizTalk DevOps: Monitor your BizTalk environment using PowerShell – SQL Agent Jobs Monitoring (Part 2)

Posted: April 6, 2015 in BizTalk, PowerShell
Tags: , , , , ,

One of the principal needs for BizTalk Administrators is the ability to monitor the health of BizTalk environments on a regular basis and react promptly to solve any possible issues that may appear in order to keep your BizTalk Server applications accessible to your users/organization.

When monitoring BizTalk Server, keep these points in mind:

  • Your infrastructure could be healthy, but your applications might not be (for example, they are receiving invalid messages and are unable to process them).
  • Your infrastructure could be unhealthy, but your applications might be running fine (for example, if a server is down, but there are enough servers assigned to the host to take over the load).
  • An infrastructure problem could surface as an application problem (for example, messages are not being processed fast enough because a server is down).

Source: Monitoring BizTalk Server

You can accomplished this by using certain tools such as: BizTalk Administration Console; BizTalk360; SCOM and many more… However, unfortunately many times, some of these tools are not available for us but we still need to accomplish this task.

Since BizTalk Server 2010 the product brings a job (Monitor BizTalk Server) that monitors the health of your environment identifying any known issues:

  • Messages without any references
  • Messages without reference counts
  • Messages with reference count less than 0
  • Message references without spool rows
  • Message references without instances
  • Instance state without instances
  • Instance subscriptions without corresponding instances
  • Orphaned DTA service instances
  • Orphaned DTA service instance exceptions
  • TDDS is not running on any host instance when global tracking option is enabled.

However this is not enough…

So how can PowerShell help us?

In some of my previous post:

I demonstrated how we could use PowerShell to monitor some aspects or features of your BizTalk environment including a script to monitor BizTalk Jobs, however this script will only monitor if the last execution of the job was successfully or not and if not send a notification.

Although this is a useful, at least in my opinion, in the last two month I found three additional situations that are important to monitor:

  • Some of the jobs were disable
  • MessageBox_Message_Cleanup_BizTalkMsgBoxDb was enable
  • SQL Server Agent was running, BizTalk Jobs were correctly enable… but despite all that no job was running (the last execution has been 7 days back – coinciding with an intervention in the system)

So I decided to create another PowerShell script. With this script you can be able to monitoring SQL Agent Jobs in your BizTalk environment using PowerShell, checking:

  • If the jobs are being executed according to the schedulers that are configured
  • If all jobs (with the exception of MessageBox_Message_Cleanup_BizTalkMsgBoxDb) are enable
  • If MessageBox_Message_Cleanup_BizTalkMsgBoxDb is disable

This script allows you to set:

  • The Jobs execution timeframe and configurations
WITH MostRecentSched AS
 -- For each job get the most recent scheduled run date (this will be the one where Rnk=1)
 SELECT job_id,
		RANK() OVER (PARTITION BY job_id ORDER BY last_executed_step_date DESC) AS Rnk
 FROM sysjobactivity
 select name [Job Name],
	last_executed_step_date [Last Scheduled Run Date],
        DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) AS [Minutes Delayed],
		CASE WHEN enabled=1 THEN 'Enabled'
          ELSE 'Disabled'
        END [Job Status]
from MostRecentSched MRS
JOIN   sysjobs SJ
ON     MRS.job_id=SJ.job_id
where Rnk=1
and ((
		((name = 'Backup BizTalk Server (BizTalkMgmtDb)' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 17))
		OR (name = 'CleanupBTFExpiredEntriesJob_BizTalkMgmtDb' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 722)
		OR (name = 'Monitor BizTalk Server (BizTalkMgmtDb)' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 11520)
		OR (name = 'Rules_Database_Cleanup_BizTalkRuleEngineDb' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 62)
		OR (name = 'MessageBox_UpdateStats_BizTalkMsgBoxDb' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 7)
		OR (name IN ('DTA Purge and Archive (BizTalkDTADb)','MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb', 'MessageBox_Parts_Cleanup_BizTalkMsgBoxDb', 'Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb', 'PurgeSubscriptionsJob_BizTalkMsgBoxDb', 'TrackedMessages_Copy_BizTalkMsgBoxDb') and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 1)
	) OR (name IN ('Backup BizTalk Server (BizTalkMgmtDb)','CleanupBTFExpiredEntriesJob_BizTalkMgmtDb',
	               'DTA Purge and Archive (BizTalkDTADb)', 'MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb',
				   'MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb', 'MessageBox_Parts_Cleanup_BizTalkMsgBoxDb',
				   'MessageBox_UpdateStats_BizTalkMsgBoxDb', 'Monitor BizTalk Server (BizTalkMgmtDb)',
				   'PurgeSubscriptionsJob_BizTalkMsgBoxDb', 'Rules_Database_Cleanup_BizTalkRuleEngineDb',
				   'TrackedMessages_Copy_BizTalkMsgBoxDb') AND SJ.enabled = 0)
	  OR (name = 'MessageBox_Message_Cleanup_BizTalkMsgBoxDb' AND SJ.enabled = 1)
order by name, last_executed_step_date desc;
  • And configure your email notification settings
#Set mail variables
[STRING]$PSEmailServer = "smtp"
[STRING]$Subject = "BizTalk Job Monitor"
[STRING]$From = ""
[array]$EmailTo = ("")

if($count -gt 0)
    #Send mail
    foreach ($to in $EmailTo)
        $Body = $HTMLmessage
        $SMTPClient = New-Object Net.Mail.SmtpClient($PSEmailServer)
        $message = New-Object Net.Mail.MailMessage($from, $to, $Subject, $Body)
        $message.IsBodyHtml = $true;

Report sample:

BizTalk-SQL-Jobs-another-way-monitorNote: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them.

Credits: Special thanks to José Dias who helped me developing this SQL Script.

The script can be found and download on Microsoft TechNet Gallery:
Another way to monitor BizTalk SQL Agent Jobs with PowerShell (2.8 KB)
Microsoft TechNet Gallery

  1. Leonid Ganeline says:

    Hi Sandro,
    It is a great addition from the real life experience!
    Thank you for sharing!

  2. Very well drafted and extremely useful.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s