Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

Configuring SQL Agent Jobs to Write to Windows Event Log

MSSQLTips author Thomas LaRock By:   |   Read Comments (4)   |   Related Tips: More > SQL Server Agent

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:

Column Name

Description

notify_level_eventlog The possible notifications include the following:
    0 - No logging
    1 - Log on success
    2 - Log on failure
    3 - Log on either result

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



Last Update: 10/23/2009


About the author
MSSQLTips author Thomas LaRock
Thomas LaRock is a seasoned IT professional with over a decade of experience. He is a Senior DBA for Confio Software and SQL Server MVP.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Friday, October 23, 2009 - 8:49:25 AM - GaryMazzone Read The Tip

There are times that I want to do this a little differently.  I want the job to test if a condition exists at the end.  Say I have the job configured to remove any date from a table that is older the 30 days.  I add a step that will check that condition for me (T-SQL statemets in the job step).  If the condition is that the deletes di not complete the delete I don't want the job to report failure of the job just that not all data was deleted. 

 

Like this:

 

Declare @daysLeftIn int
SELECT  @daysLeftIn = DATEDIFF(dd, CreateDate, GETUTCDATE())
FROM    tablename WITH ( NOLOCK )
WHERE   Pkfield IN (
        SELECT  MIN(PkField) AS fieldname 
        FROM    tableName WITH ( NOLOCK ) ) ;

If @daysLeftIn > 30
 RAISERROR  (N'The delete of records in the tableName table to 30 days has failed',
    19,1)WITH LOG;

 

This will mark the job as compelted with success which is true, the job did complete.  But enter a Windows Application Log entry for failure since the delete did not work


Friday, October 23, 2009 - 8:55:00 AM - admin Read The Tip

Gary,

Thank you for the alternative logic and insight. 

Does anyone else from the community have a different way to approach the situation?

Thank you,
The MSSQLTips Team


Friday, August 03, 2012 - 8:24:45 AM - Jan Read The Tip

Hi!

What i'm locking for is the possibility to send SQL Server Logs NOT to the application event. Instead i like to log the events in a seperated eventlog container e.g. SQL-Eventlog. Do you know how to handle this?


Wednesday, March 06, 2013 - 10:35:03 AM - Orest Read The Tip

How about using SQL Management Pack under SCOM. I searched for a sollution but nothing came up, the only thing i found was:

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/2c79d5b1-c773-43b5-b473-51383772f2a2/



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.