Check for Failed Jobs
This script allows you to check whether any scheduled jobs have failed and email the errors to administrator(s). Unlike normal SQL Server job error notifications, this script shows all the job failures in a table.
- Requires Database Mail enabled.
- Requires stored procedure SaveTableAsHTML.
- Copy the script below.
- Create a new job in SQL Server Agent called "Check Failed Jobs" for example.
- Copy the script excluding the comments and paste into the new step.
- Change the configuration variables to suit you requirements.
IMPORTANT: The @FailedMinutesAgo variable is set to be the number of minutes interval when the job is next scheduled to reoccur. For the @FailedMinutesAgo variable, I use 5 minutes as a guide but it depends upon your requirements. If you specify less time than when the job actually last ran then you will only pickup errors for the time specified (ie. you will miss out in finding all potential errors since the job last ran). If you specify more time than when the job last ran then you may be notified of errors that were already previously detected.
- Schedule the job to reoccur at the same interval as specified for the @FailedMinutesAgo variable.
- Save and enable the job.