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
2 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?