Select Page

Quick Scan Report – Job Without Failure Notify

The Quick Scan Report has detected that you have jobs configured that don’t have any alerting when they fail. It is a good practice to send out an alert email when a job fails so that you will know what fails.

Here is a query that will generate tsql code to add the first operator as the notification operator.

SELECT 'Agent Job with no notifiction on falure. [' + j.[name] + ']',
		'EXEC msdb.dbo.sp_update_job @job_id=N''' + cast(j.job_id as varchar(1000)) + ''', @notify_level_email=2, @notify_level_netsend=2, @notify_level_page=2, @notify_email_operator_name=N''' + (select top 1 name from msdb.dbo.sysoperators where enabled = 1) + ''';' as FixScript
  FROM [msdb].[dbo].[sysjobs] j
  LEFT JOIN [msdb].[dbo].[sysoperators] o ON (j.[notify_email_operator_id] = o.[id])
 WHERE j.[enabled] = 1
   AND j.[notify_level_email] NOT IN (1, 2, 3)