Alerting When SQL Agent Jobs Fail

John Shaulis has a script to send out e-mail alerts on SQL Agent job failures:

Here we have a T-SQL statement that we can either create as a stored procedure or just run as T-SQL in a job step. It looks back over the last 10 minutes and looks for job failures. I would recommend scheduling this T-SQL to run every 5 minutes, you will get duplicate entries for a short period of time, but ideally, you shouldn’t get any failures anyway right? Plus once you’re notified, you can turn this off while you work on it or you can specify in the where clause to remove this job until fixed.

Click through for the script and do read the instructions.

Related Posts

Hidden Patterns in SQL Agent Outputs

Solomon Rutzky has found patterns which cause SQL Agent step outputs not to write any details: And that’s when it happened: I started getting a value back from QUOTENAME(ORIGINAL_DB_NAME()), and not just the square brackets with nothing between them. The name of a database was now between the square brackets. Why wasn’t that showing up before? Solomon […]

Read More

Replacing Text Across SQL Agent Jobs

Max Vernon shares a script to perform a find-and-replace across SQL Agent jobs: Once in a while you might need to make common changes to a lot of SQL Server Agent Jobs. For example, if you change the path where you store SQL Server backup files, you might need to update many jobs to point at \\SERVERB\Backups instead […]

Read More

1 Comment

  • Jason on 2019-06-15

    The concept sounds good but I’m curious why the notification for the job itself wouldn’t just be used. I can see this as a backup if a notification hasn’t been set up or maybe to avoid notification setup all together. Thank you for the article.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

June 2019
MTWTFSS
« May  
 12
3456789
10111213141516
17181920212223
24252627282930