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