Mark Wilkinson shows how to set up a SQL Agent job failure monitoring solution:
Since we are storing the date the records are added to the table, this query will always return the latest set of failures. This is a simple example, but the possibilities are endless:
-
Send the results of this query via database mail
-
Join with
dbo.sysjobsanddbo.syscategories, alerting on different thresholds per job category -
Extend the
TOP (1)to include multiple capture periods and alert on average failures per capture
Check it out. This is particularly helpful if you get blasted with thousands of error messages per minute because somebody made a bunch of untested changes and broke every job in your environment and caused the mail server to throttle your account for a multi-day period. Not that this has ever happened to me, of course…