SQL Agent Jobs With Notification Enabled

January 8, 2016

DBA team that I worked with manage 170+ SQL Servers. We do not enable notification on any SQL Agent job. We centrally monitor all jobs and send a notification based on the tier (Business-critical). Last night on-call DBA got paged for a job failed in the server we are doing proof of concept. By looking at the configuration in the central server it should not send pager notification. Then I realized it was set in the job itself. This server has over 200 SQL Agent jobs. First I need to find out which jobs have notification enabled, what kind of notification and notify level (success/fail/complete).

Instead of checking each one from GUI I wrote a quick script to find all 3 pieces of information I needed. Thought of sharing in case somebody needs the same.

USE [msdb];
GO

SET NOCOUNT ON;

SELECT 'SQL Agent job(s) with notification enabled' AS [Message];

SELECT 
  j.[name] AS [JobName] ,
  emailWhen = 
  CASE 
    WHEN j.[notify_level_email] = 0 THEN 'Never'
    WHEN j.[notify_level_email] = 1 THEN 'Succeeds'
    WHEN j.[notify_level_email] = 2 THEN 'Fails'
    WHEN j.[notify_level_email] = 3 THEN 'Completes'
  END ,
  eventLogWhen = 
  CASE 
    WHEN j.[notify_level_eventlog] = 0 THEN 'Never'
    WHEN j.[notify_level_eventlog] = 1 THEN 'Succeeds'
    WHEN j.[notify_level_eventlog] = 2 THEN 'Fails'
    WHEN j.[notify_level_eventlog] = 3
    THEN 'Completes'
  END ,
  netsendWhen = 
  CASE 
    WHEN j.[notify_level_netsend] = 0 THEN 'Never'
    WHEN j.[notify_level_netsend] = 1 THEN 'Succeeds'
    WHEN j.[notify_level_netsend] = 2 THEN 'Fails'
    WHEN j.[notify_level_netsend] = 3 THEN 'Completes'
  END ,
  pageWhen = 
  CASE 
    WHEN j.[notify_level_page] = 0 THEN 'Never'
    WHEN j.[notify_level_page] = 1 THEN 'Succeeds'
    WHEN j.[notify_level_page] = 2 THEN 'Fails'
    WHEN j.[notify_level_page] = 3 THEN 'Completes'
  END
FROM [dbo].[sysjobs] j
WHERE j.[enabled] = 1
  AND ( j.[notify_level_email] <> 0
  OR j.[notify_level_eventlog] <> 0
  OR j.[notify_level_netsend] <> 0
  OR j.[notify_level_page] <> 0
      );
GO

The following code can be used to disable notification for any SQL Agent job.

USE [msdb];
GO

DECLARE @name sysname= N'DBA User Tlog';

EXEC msdb.dbo.sp_update_job 
@job_name = @name,
@notify_email_operator_name = N'',
@notify_page_operator_name = N'';
--comment out following line to disable netsend
--, @notify_netsend_operator_name = N''
--comment out following line to disable writing entry into Windows application log
--, @notify_level_eventlog = 0
GO

3 replies on “SQL Agent Jobs With Notification Enabled”

Hi Sir,

I would like to monitor the 200(Approx) SQL jobs health checks every 5 mins and if any job failed in the last 15mins, it should trigger the alert.
Could you please help me on this request ? like above script which you mentioned.

Thanks in advance.

All 200 jobs are residing in one server?

Thanks for the code. Here’s what I turned it into … because who wants to be spammed by unimportant jobs (that run frequently) when they fail.

— JobNotificationEmailStop.sql

SELECT S.[name] JobName,
SS.freq_subday_interval [ScheduleFrequency(mins)],
‘EXEC msdb.dbo.sp_update_job @job_name = N”’ + S.[name]
+ ”’, @notify_email_operator_name = N””;’ CommandToDisableEmailNotification
FROM msdb.dbo.sysjobs S
JOIN msdb.dbo.sysjobschedules SJ ON S.job_id = SJ.job_id
JOIN msdb.dbo.sysschedules SS ON SS.schedule_id = SJ.schedule_id
WHERE SS.freq_subday_interval > 0
AND S.notify_level_email > 0
ORDER BY SS.freq_subday_interval;