The Backup Job Said It Succeeded
Did it though?
SQL Agent job failures are easy to miss. They fire at 3AM, the notification email goes to a shared mailbox nobody checks, and by morning everyone assumes it worked.
Until it didn’t — and now you’re in an incident trying to restore from a backup that never ran.
This query gives you a real-time view of every job: last run status, duration, failure message, and next scheduled execution.
What This Query Does
Joins across four msdb tables:
| Table | Purpose |
|---|---|
sysjobs | Job definitions and enabled state |
sysjobactivity | Most recent activity per job in this agent session |
sysjobhistory | Full run history with outcome and messages |
sysschedules | Next scheduled run time |
Results are sorted with failed jobs at the top so the problems are immediately visible.
The Last Message column shows the actual error text — no need to drill into job history manually.
SQL Agent Job Health Script
SQL Agent Job Health.sql
Loading…
Gareth Winterman