SQL Jobs

SQL Jobs, also known as SQL Server Agent Jobs, are a mechanism in Microsoft SQL Server that allows users to automate administrative tasks, such as database maintenance, data backups, data imports/exports, and other routine operations. 

Monitoring SQL Jobs allows businesses to detect and diagnose issues related to job failures, timeouts, or errors in real-time. Prompt identification of job-related issues enables administrators to initiate troubleshooting steps, such as rerunning failed jobs, investigating error logs, or adjusting job schedules, to minimize service disruptions and maintain the reliability of SQL Server environments.

BizTalk Server heavily relies on its SQL Server databases. The health of these databases is crucial for the health of BizTalk Server. Out of the box, BizTalk Server comes with a number of SQL Server jobs that perform all kinds of maintenance tasks, thereby contributing to the health of those databases. BizTalk360 allows you to monitor those SQL jobs. There are two threshold options:

• Expected Job State - Monitor if the SQL job is runnable (Enabled) according to its schedule.
• Expected Last Run State - Monitor if the SQL job is finishing successfully.

The best practices for monitoring BizTalk Server jobs are that all jobs should be Enabled and that the last run was Successful, but with a few notable exceptions:

• MessageBox_Message_Cleanup_BizTalkMsgBoxDb job should be disabled.
• MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job never ends with success.

Setting up monitoring for SQL jobs

Users can follow the below steps to configure the monitoring of SQL jobs:

1. Select the SQL Server instance for which you want to set up the monitoring on SQL jobs and select the alarm from the drop-down for which they would like to associate the alerts.
2. Users can select a single or multiple SQL jobs that they wish to monitor.
3. And they can set the value of 'Expected Job State' by selecting the appropriate value from the drop-down box and 'Expected Last Run State' after selecting Expected Job State from the drop-down. The monitor status is set based on the current and expected stat

Autocorrect:  If there is a mismatch between the current state and the expected state of the SQL Jobs you can set the autocorrect to match the state.