SQL Script to monitor BizTalk Server SQL Jobs

For those who’ve been following my work, you might recall that I shared a set of PowerShell scripts a while back for monitoring BizTalk Server. One of these scripts was designed to oversee BizTalk Server SQL Jobs, and you can find additional details about it here: BizTalk DevOps: Monitor your BizTalk environment using PowerShell – SQL Agent Jobs Monitoring (Part 2) and here: Monitor your BizTalk environment using PowerShell – SQL Agent Jobs Monitoring.

Within those blog posts, I also provided at least one SQL script for monitoring the status of jobs – whether they were disabled or running as anticipated, such as every minute, every fifty minutes, and so forth. However, it has come to my attention this week that even though they were enabled and running as planned, some of them were not finishing with success, and I was not notified. For this reason, I decided to create a second SQL script to enhance the functionality of the earlier one. This new script is designed to search for any failures that have occurred in the last hour. It will then present me with the most recent failure details, including the error message, for each of the Jobs in an error state.


Hope you find this helpful! So, if you liked the content or found it helpful and want to help me write more content, you can buy (or help buy) my son a Star Wars Lego! 

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Leave a Reply

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


Back to Top