I was recently customizing a rule and some notifications in System Center Operations Manager to provide alerts when a SQL Server Job failed then automatically create a work order in our ITIL solution. I do this by looking in the server’s event log for failed job events.
After completing and testing the SCOM rule and notifications I noticed that some failed jobs were not ending up as work orders. After digging around a little I found that some jobs were created without configuring the notifications to write to the event log. This was the case on multiple servers.
I created this little query that can be used on a single server or on a multi-server query to find all jobs that are not configured to write to the event log and update them to write to the event log on failure. This script will work on SQL 2005 and later versions.
DECLARE @JobName sysname DECLARE @NotifyLevelEventLog int SET @NotifyLevelEventLog = 2 DECLARE jobsNotReportingToEventLog CURSOR FAST_FORWARD FOR SELECT JobName = name FROM msdb..sysjobs AS s WHERE notify_level_eventlog = 0 OPEN jobsNotReportingToEventLog FETCH NEXT FROM jobsNotReportingToEventLog INTO @JobName WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Setting notify_level_eventlog on ' + @JobName + ' to ' + CAST(@NotifyLevelEventLog AS varchar(4)) EXECUTE msdb.dbo.sp_update_job @job_name = @JobName, @notify_level_eventlog = @NotifyLevelEventLog FETCH NEXT FROM jobsNotReportingToEventLog INTO @JobName END DEALLOCATE jobsNotReportingToEventLog