Free SQL Help for Devs, DBAs, and the Curious

SQL Agent Job Monitoring is a vital part of maintaining an efficient and smooth-running database system. With the ability to monitor the performance of your SQL Agent Jobs, you can identify and remedy potential issues before they become problematic. In this blog post, we will explore how to set up custom alerting for SQL Agent Job Monitoring using technologies from SQL Server 2012, SQL Server 2014, MySQL 5.6, DB2 10.5, and Azure SQL.

The first step in setting up custom alerting for SQL Agent Job Monitoring is to create a job that will monitor your SQL Agent Jobs. In SQL Server 2012 and 2014, this can be done using the SQL Server Agent. You can configure the job to execute a script that checks the status of all SQL Agent Jobs and sends an alert if any jobs are failing.

BEGIN TRANSACTION
CREATE JOB [MonitorSQLAgentJobs]
@description=N'Monitor SQL Agent Jobs',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'Alerts'
COMMIT

For MySQL 5.6, we can use the Event Scheduler to create a similar job. The event will regularly execute a script that checks the status of all jobs and sends an alert if any jobs are failing.

CREATE EVENT MonitorJobs
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
DECLARE failed_jobs INT;
SELECT COUNT(*) INTO failed_jobs
FROM information_schema.events
WHERE status = 'FAILED';
IF failed_jobs > 0 THEN
CALL send_alert(failed_jobs);
END IF;
END

For DB2 10.5, we can use the ADMIN_TASK_ADD procedure to create a task that checks the status of all jobs and sends an alert if any jobs are failing.

CALL ADMIN_TASK_ADD ('MonitorJobs',
'CALL CHECK_JOBS()',
'* * * * *',
'',
'',
'',
'',
'')

Azure SQL does not have a built-in job scheduler like SQL Server Agent or MySQL Event Scheduler. However, we can use Azure Automation to create a runbook that checks the status of all jobs and sends an alert if any jobs are failing. The script can be written in PowerShell and can leverage the AzureRM.Sql module to interact with Azure SQL.

As a part of the monitoring process, you may want to set up alerts for specific events or conditions. Custom alerts can be set up to notify you when a job fails, when a job takes longer than expected to complete, or when a job hasn't run at its scheduled time. This way, you can be proactive in your approach to managing your SQL Agent Jobs and ensure that your database system is running efficiently and effectively.

In conclusion, SQL Agent Job Monitoring with custom alerting is a powerful tool in maintaining an efficient database system. By creating jobs that check the status of SQL Agent Jobs and send alerts when necessary, you can ensure that potential issues are identified and remedied quickly. Regardless of the database technology you are using, setting up custom alerting for SQL Agent Job Monitoring is a beneficial practice for any database administrator.