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;