SQL Jobs Writing to the Event Log

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

Leave a Reply

Your email address will not be published. Required fields are marked *