SQL Agent Jobs With Notification Enabled

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 notification based on the tier (Business critical). Last night on call DBA got paged for a job failed in server we are doing proof of concept. By looking at the configuration in the central sever 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 has 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 need the same.

Taiob M Ali
Date: Januray 8 2016

USE [msdb];
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'
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


Following code can be used to disable notificatoin for any SQL Agent job.

USE [msdb];

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