SQL Server Agent Jobs that Have No Notification and Notification Change

SQL Server Agent Jobs that Have No Notification and Notification Change

If you want to verify each of your SQL Agent jobs have email notification then you can run this code:

USE msdb

SELECT name, notify_email_operator_id  

FROM dbo.sysjobs 

This code will display each SQL Server Agent job that doesn’t have an operator associated with an email notification operator.

USE msdb

SELECT name, notify_email_operator_id  

FROM dbo.sysjobs 

WHERE notify_email_operator_id = 0;

OR

SELECT @@servername as Server_name, j.name AS JobName,

      e.name AS EmailOperator

         ,j.enabled As Enabled

FROM msdb.dbo.sysjobs j

  LEFT JOIN msdb.dbo.sysoperators e

     on j.notify_email_operator_id = e.id

        where j.enabled=1

1=enabled

0=Disabled

Based on requirement please change the where condition.

IF server doesn't have any operator use below script to create a new operator. Below script I created an operator named "DBA-SQL" and associated it with the email address “DBA-SQL@Company.com” or else use GUI from SSMS.

USE [msdb]

GO

EXEC msdb.dbo.sp_add_operator @name=N'DBA-SQL',

                    @enabled=1,

                    @email_address=N'DBA-SQL@compenay.com'

GO

Automatically Updating SQL Agent Jobs to Have Notification:

If you already have a number of SQL Server agent jobs in one server where notification hasn’t been set up you might want to set up each job to notify someone.   

If you have a lot of SQL Agent jobs it might take a while for you to go through each job to define an email notification operator manually through the SQL Agent GUI.  

A better way to change a bunch of SQL Agent jobs to send email notifications would be to run a script.  

Here is a sample script for defining an operator on each job that doesn’t already have an email operator:

USE [msdb]

SET NOCOUNT ON;

DECLARE @Operator varchar(50) = 'DBA-SQL' -- 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 Faulure,3=always

FROM dbo.sysjobs j

WHERE j.enabled = 1

AND j.notify_level_email <> 1

This script doesn’t actually add the operators to SQL Agent jobs that don’t have email notification.   But instead it just creates a series of EXEC statements that when executed add the email notifications.  The series of EXEC statements that this script generates will run the sp_update_job system stored procedures to add an email operator.  This particular script only adds email notification on a job that doesn’t already have email notification.  Additionally, it only sets up notification to occur when the job execution fails.   If you should happen to want different operators to be notified for different jobs then you will either need to add some special code to place the appropriate operator on each job, or run the above script then modify the operators for each job prior to executing the generated script.

If you have multiple servers to change the notification use below script and run using batch command or power-shell.

set nocount on

DECLARE @strsql    NVARCHAR(MAX)

DECLARE @job_list cursor

set @job_list = cursor for

SELECT     'EXEC msdb..sp_update_job @job_name = ''' + j.[name] + ''', @notify_email_operator_name = ''DBA-SQL'', @notify_level_email = 2' [exec_sql]

FROM msdb.dbo.sysjobs j

  LEFT JOIN msdb.dbo.sysoperators e

     on j.notify_email_operator_id = e.id

        where j.enabled = 1 --Change according to you requirement

      

open @job_list

fetch next from @job_list into @strsql

while @@FETCH_STATUS = 0

begin

       exec sp_executesql @strsql

       fetch next from @job_list into @strsql

end

close @job_list

deallocate @job_list

Please test in test\dev server then use in prodcution.