Configuring SQL Agent Jobs to Write to Windows Event Log

Problem

SQL agent jobs are not created with notifications enabled by default. When creating a new job from within SQL Server Management Studio, you will see the following screen when navigating to the Notifications page.

You have several options on the Notifications page. You could send an email, a page, a net send, write to the application event log, or you could delete the job (which is hardly what I would call a ‘notification’).

A best practice for job management would be to write information to the application event log in the event of a job failure. Some monitoring tools and software, such as Operations Manager, relies on information being written to the event logs. If no information is being logged than it is possible you could be missing some valuable alerts.

In this tip I will go over how to set this up and also a simple way to turn this on for all of your jobs.

Solution

When creating SQL Agent jobs, make certain to configure the job to write to the application event log when the job fails.

Run this to find details on the event notifications for the jobs currently defined on your instance:

 SELECT [name], [notify_level_eventlog]

 FROM msdb..sysjobs

A sample result set is as follows:

The sample result set lists the four possible choices for the [notify_level_eventlog] column:

Failures are written to the event log with an eventID of 208. This is important for something like Operations Manager, which has a rule inside the SQL Management Pack that relies on job failures being written to the application event log.

Operations Manager also has a monitor for the last run status of a SQL Agent job.

This monitor does not raise an alert, however. It merely changes the health status of the SQL Agent job target. So you would need to be in the habit of going to your database state views in order to monitor for any failed jobs. Most people, myself included, would prefer to have the failures logged in the application event log and also to generate some email notifications as a reminder.

You can run the following query to find all the jobs defined on your instance that are not doing any logging.

 SELECT [name]

 FROM msdb..sysjobs

 WHERE [notify_level_eventlog] = 0

If you prefer and are using SQL Server 2008, you could run that same query against all the instances registered in your Central Management Server.

From there, you can update your jobs to write to the application event log on a job failure with one statement.  I like to filter my update statement based upon the name of the job. If you prefer, you can remove the WHERE clause and simply update all of your jobs at the same time.

 UPDATE msdb..sysjobs

 SET [notify_level_eventlog] = 2

 WHERE [name] like ‘This one%’

You will want to be careful if you choose to do so. I have seen some applications that create jobs with a schedule that runs the job every minute. You may not want to write that many failures to your application event log, so be mindful of job schedules when enabling your job notifications.

Next Steps

• Run the sample code to determine how many jobs currently exist that are not logging any information to the application event log.
• Take a look at these other tips related to this topic:
  • Auto alert for SQL Agent jobs when they are enabled or disabled
  • An alternative to SQL Server email alerts
  • Setting Up Alerts for All SQL Server Agent Jobs

Thomas LaRock

Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry in roles including programmer, developer, analyst, and database administrator.

LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010. While at Confio, his research and experience helped to create the initial versions of the software now known as SolarWinds Database Performance Analyzer. LaRock joined the SolarWinds family through the acquisition of Confio in 2013.

LaRock is also the Immediate Past President of the Professional Association for SQL Server (PASS) and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses his time working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle, MySQL, SAP, and DB2, making it his mission to give IT and data professionals longer weekends.