Forum Discussion

Mike_Suding's avatar
Mike_Suding
Former Employee
9 years ago

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

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


 

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