Fixing Jobs With No Email Notification

In the last post we found jobs that had no email notification. Once you find them, how do you fix this problem?

I’m going to assume that database mail is already enabled and configured on the SQL Server instance.
1. Create an operator, assuming one does not exist.

This can be done either through T-SQL or through the GUI.  In the GUI, right click the Operator folder under SQL Server Agent and chooser “New Operator.”  Then fill in the details of operator “Name “and “Email name.”  See below for an example.

Then you click OK in the bottom right of the GUI to finish the process.

Alternatively you can use T-SQL to create the Operator:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'DBA Team', 
    @enabled=1, 
    @pager_days=0, 
    @email_address=N'[email protected]'
GO

2. Assign that operator to multiple jobs via T-SQL.

Once there is an Operator to assign to jobs with no email notifications, the next step is to actually add the new Operator to the applicable SQL Agent jobs.  This will create the T-SQL that can be copied and ran in a new SSMS window, allowing you to confirm before running the statements.

Keep in mind that in this example I am only updating jobs that don’t already have an operator assigned to them.  This makes the WHERE clause very important.  If you leave this off, copy and run the generated statements, then all jobs will be updated to whatever was specified in the variable.  This may not be what you want for your environment, so please be sure to use the WHERE clause and choose notify_level_email = 0 if you only want to update jobs that have no operator assigned to them.

use msdb
GO
DECLARE @operator varchar(50)
SET @operator = 'DBA Team' 

SELECT 'EXEC msdb.dbo.sp_update_job @job_ID = ''' + convert(varchar(50),J.job_id) + ''' ,@notify_level_eventlog = 2, @notify_level_email = 2, @notify_email_operator_name = ''' + @operator + '''' 
FROM sysjobs As J
 INNER JOIN sysjobschedules AS JS ON J.job_id = JS.job_id
 INNER JOIN sysschedules AS SS ON SS.schedule_id = JS.schedule_id
 
WHERE J.enabled = 1 --Job is enabled
 AND J.notify_level_email = 0 --never notify
 AND SS.enabled = 1; --it has a schedule that is enabled

Notice that I am using sp_update_job not only to add the email operator, but also to set the job to log to the Windows event log in case of a failure.  Why?  Well, what happens if database mail is not configured properly?  Maybe it worked when the job was created, or maybe it worked the last time you touched database mail, but what if someone else made changes to it?  In that case, you might not get the expected email when the job fails.

If you have someone, like a Junior DBA, who is looking through Windows Event logs for issues they might find it.  Or what if you have one of those slick 3rd party monitoring that scans for errors and notifies you when it finds one?  Then you have another way to be notified that the job failed.

And here is your BOL link to all the goodies involved with sp_update_job.