Part of the DBA-Tools Project.
A SQL Agent job that silently stopped running is one of the quietest failure modes in SQL Server, nothing errors, nothing alerts, the maintenance work it used to do just stops happening until someone notices the symptom weeks or months later: a fragmented index that never got rebuilt, an alert nobody would have received anyway because no operator was ever configured. Four scripts on this site cover the full job health picture, from a general inventory to specific failure detection to whether anyone would even be notified if something did fail.
This post is the map. It groups them by the question each answers and lays out a sensible order for a routine job health pass.
Why SQL Agent and Jobs Matters
- A job that stops running produces no error by itself, silence is the failure mode, which is exactly why it needs an active check rather than waiting for an alert
- Alerts and operators are two separate configurations that both have to exist for anyone to actually get notified, a severity-based alert with no operator attached notifies nobody
- Maintenance jobs specifically (index rebuilds, integrity checks, backups) tend to be set up once and never revisited, exactly the kind of thing that silently degrades over time
- A general job inventory and a failure-specific check answer different questions, “what’s configured” and “what’s actually failing” aren’t the same thing
The Scripts, Grouped by Question
What jobs exist, and what’s their general health?
- SQL Agent Job Overview, every job on the instance, enabled state, schedule, and last run outcome, the starting inventory
What’s actually failing right now?
- SQL Agent Job Failures, filters straight to job failures, so a genuine problem doesn’t get lost in a long list of jobs that are running fine
Would anyone actually be notified if something failed?
- Agent Alerts and Operators, checks whether severity-based alerts are configured and whether an operator is actually attached to receive them, a gap here means real failures generate no notification at all
Is routine maintenance actually still running?
- Maintenance Job Status, specifically for the jobs that keep an instance healthy day to day, index maintenance, integrity checks, backups, confirming they’re not just configured but genuinely completing
How They Fit Together
A sensible order for a routine job health pass, or when inheriting an unfamiliar instance:
- Start with the overview to see the full job inventory, what exists, what’s enabled, what schedule it runs on
- Check for active failures next, filtering straight to what’s actually broken right now rather than reading through every healthy job to find it
- Check alerting configuration, since a job failure that generates no notification is functionally the same as no monitoring at all, confirm alerts and operators are both actually in place
- Check maintenance jobs specifically, since these are the ones whose silent failure has the most compounding cost, a maintenance job that stopped running months ago means index health and integrity checks have been quietly degrading the whole time
Best Practices Across the Series
- Treat “job exists and is enabled” and “job is actually succeeding” as separate questions, always check both
- Confirm every severity-based alert has a real operator attached, an alert with no recipient is a false sense of security
- Re-check maintenance job status specifically after any server move or Agent service account change, these are common points where a job silently stops running without anyone noticing
- Run the failure-specific check on a schedule, not just when a symptom already surfaced, catching a failure the same week it starts is much cheaper than catching it months later
See Also
This pillar is one of ten in DBA Scripts: The Complete Guide, the map across the whole series organized by the question you’re actually asking.
Summary
SQL Agent job health has a quiet failure mode: nothing errors when a job silently stops running, or when an alert has no operator to actually notify. These four scripts turn that silence into a direct answer, what’s configured, what’s failing, who’d actually be told, and whether the maintenance work an instance depends on is genuinely still happening.
Start with the Job Overview for a full inventory, then check Job Failures and Agent Alerts and Operators together, configuration and actual notification are two different things worth confirming separately.
Leave a Reply