If you are a database administrator (DBA) responsible for running and monitoring SQL Server Agent jobs, you know how important it is to stay on top of job statuses. In this article, we will discuss some automated approaches to monitoring SQL Server Agent jobs.

SQL Server Agent Notifications

There are three different types of SQL Server Agent notifications you can set up: Email, Pager, and NET SEND. However, Microsoft recommends not using the Pager and NET SEND options as they have been deprecated. Therefore, we will focus on the Email option.

You can set up notifications for when a job succeeds, fails, or completes. If you only want to be notified when a job fails, you can set up the notification to only occur in that case. If you want to be notified if the job succeeds or fails, you can set it up to notify you when the job completes.

When deciding which option to use for notification, consider the following:

• If you only get notified when the job completes successfully, how do you know when the job fails?
• If you get notified when the job completes, you will receive notifications for both successful and failed runs, which can lead to a lot of notifications.
• If you are only concerned about failures, it is best to only get notified for failures.

Detecting SQL Server Agent Jobs with No Notification

If you have numerous SQL Server Agent jobs on your server instance, you may want to know which ones do not have notifications set up. You can easily detect if a job is set up to notify someone via Email by reviewing the “notify_level_email” value in the “sysjobs” system table in the “msdb” database.

Here is an example code that displays the names of the SQL Server Agent jobs that are not set up to send Email notifications:

USE msdb;
GO
SELECT name FROM dbo.sysjobs WHERE notify_level_email = 0;

If you want to verify that each of your SQL Agent jobs has email notification, you can run this code:

USE msdb;
SELECT name, notify_email_operator_id FROM dbo.sysjobs WHERE notify_email_operator_id = 0;

Setting up an Operator

In order to send email notifications, you need to have an Operator and configure database mail. To set up an operator, you have two choices: using the GUI tool available in SQL Server Management Studio or running a script.

Here is an example script that creates a single operator:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name = N'DBA', @enabled = 1, @email_address = N'DBA@DatabaseJournal.com'
GO

Automatically Updating SQL Agent Jobs to Have Notification

If you have a large number of SQL Server Agent jobs where notification hasn’t been set up, manually defining an email notification operator for each job through the SQL Agent GUI can be time-consuming. A better approach is to run a script that updates each job to send email notifications.

Here is an example script that generates a series of EXEC statements to add email notifications to jobs that don’t already have them:

USE [msdb]
GO
SET NOCOUNT ON;

DECLARE @Operator varchar(50) = 'DBA' -- place your operator name here

SELECT 'EXEC sp_update_job @job_name = ''' + j.[name] + ''', @notify_email_operator_name = ''' + @Operator + ''', @notify_level_email = 2' -- 1=On Success, 2=On Failure, 3=Always
FROM dbo.sysjobs j
WHERE j.enabled = 1 AND j.notify_level_email <> 1
GO

This script generates a series of EXEC statements that, when executed, add email notifications to the jobs. It specifically adds email notification to jobs that don’t already have it and sets up notification to occur only when the job execution fails.

Daily Monitoring Job

Getting individual SQL Server Agent job notifications for failed jobs is one method of staying informed about the jobs that need attention. Another method is to receive a daily report of all the jobs that failed in the last 24 hours.

One way to accomplish this is by building a SQL Agent job that runs a query against the “msdb” database tables to find failed jobs. Once the failed jobs are identified, you can email a report to the DBAs.

By combining individual job notifications with a daily report, you have a comprehensive view of all the job failures in the last 24 hours. This is especially useful for quickly identifying failures during batch windows when you may not be actively monitoring individual emails.

Summary

Being a proactive DBA involves ensuring that all SQL Server Agent jobs notify someone when they fail. By being notified of failures, you can quickly respond and resolve any issues. The scripts provided in this article can help you identify jobs that need email notification and add email notification to all your SQL Server Agent jobs.

Remember, being proactive is always better than being reactive when it comes to managing SQL Server Agent jobs.

See all articles by Greg Larsen