Adrian Buckman has a stored procedure which retrieves failed SQL Agent jobs over a given timeframe:
So here is what it does:
-
Check for failed agent jobs within the dates you specify (provided the agent log has data for this period) @FromDate will default to the past 12 hours if no value is passed in, @ToDate will default to GetDate() if no value is passed in.
-
Check that any failed jobs that have occurred within the date range have not subsequently succeeded, if the job has since succeeded since its last failure then this job will not be included in the results.
-
Check that the job is not currently running, if the job is still running at the time of executing the procedure then this job will be excluded from the results.
-
If a failed agent job exists that passes through the above checks then the Last Failure message will be obtained for the job and shown in the results along with the Job name, Failed Step and Failed Datetime.
Read on for the script.