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.
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.