• 0
Mike Suding

I wrote this "how to" article on alert when a MS-SQL 'job' fails

Question

DESCRIPTION:

This will alert you via LogicMonitor when a MS-SQL ‘job’ fails

 

INSTRUCTIONS:

  1. Create a LogicMonitor “EventSource”. Named “SQL job failures” or similar. I suggest that you clone the default “SQL eventsource”.  Set these settings:
    LogName = “Application”
    SourceName = “SQLSERVERAGENT”
    Level more urgent than “Information”

     

  2. Use Microsoft’s SQL Management Studio to set each job on each of your SQL servers so that when a job fails, it will write a message to the Windows Application event log (screenshot below)
    Right click on job and click “Properties”
    Click on “Notifications” on left pane
    Click on “Write to Windows event logs” checkbox and click “When the job fails”

     

  3. Test. I suggest you create a job with a bogus query and run it. Look in the Windows Event viewer and you should notice an EventID 208 at “warning” severity.

 

Optional:  In LogicMonitor you can create an Alert rule that notifies a specific person or team. Do this by selecting the datasource name of the eventSource you named above

 

Note: I developed this and tested it with SQL version 2008-R2 but it will probably also work with newer versions.

 

 

Below is screenshot showing how to create/clone a LogicMonitor EventSourceDyC8O4ACV1AMoD3kp0vteym3GgJZdTl4l2vaj1D5


 

Below shows how to set “Write to event log” on each SQL Job

zyr9D8p6pGdz-kAhnGAV5OerMcSbUzQ20R2clueZ

 

 

Below shows what the alert looks like in LogicMonitor.

IJebg_0JR092gguHo7QhCK-myxHwsyeFSVi7F49A


 

Share this post


Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Thanks, this is useful. Incidentally, you can no longer select "Informational" from the Level drop-down menu.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now