Home › Server Overview › SQL Server Quick Scan Report › Quick Scan Report – Job Without Failure Notify

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)

Enroll Today!
SteveStedman5
SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!