Is the Automation Healthy, and Is It Actually Doing the Job
A SQL Agent job that silently stopped running is one of the quietest failure modes in SQL Server, nothing errors, nothing alerts, the work it used to do just stops happening until someone notices the symptom weeks later. This area covers three related questions: is the automation itself healthy, is index maintenance, one of the most common things that should be automated, actually happening, and how do you build that automation as something you can read and trust rather than a black box.
Fourteen scripts across three pillars.
Start Here
The Three Pillars
⏰ SQL Agent and Jobs
What’s scheduled, whether it’s succeeding, whether it’s slowing down, and whether anyone would even be notified if it failed.
🧩 Index Maintenance
Fragmentation, missing indexes, unused indexes, and duplicate coverage, the full index health picture.
🛠️ Maintenance Job Framework
Generate readable backup, integrity, and index maintenance jobs as plain, reviewable T-SQL DDL.
Why These Three Belong Together
- SQL Agent and Jobs is the health check on the automation layer itself. Before trusting that any scheduled maintenance is happening, confirm the jobs that are supposed to run actually are, and that someone would be told if they weren’t.
- Index Maintenance is the single most common thing that silently stops happening. A missing index shows up eventually as a slow query someone complains about; fragmentation quietly degrades range scans until someone happens to check. It’s the textbook case for “automation that needs to actually be running,” not the only one.
- Maintenance Job Framework is how you build that automation in the first place, three generator scripts that produce readable
sp_add_job/sp_add_jobstepDDL for backups, integrity checks, and index maintenance, reviewable line by line before it ever runs.
How They Fit Together
- Confirm the automation layer is healthy first. SQL Agent and Jobs is a routine pass: what’s scheduled, what’s failed, whether alerting is even configured.
- If maintenance jobs don’t exist yet, or need rebuilding, Maintenance Job Framework generates them as plain DDL you review end to end rather than a call into a larger, opaque solution.
- Index health is the specific area worth checking on its own routine, even with maintenance jobs running. Index Maintenance covers fragmentation, missing indexes, unused indexes, and duplicates, the questions a generic maintenance job’s index rebuild step doesn’t fully answer on its own.
Best Practices Across This Area
- Check SQL Agent job history and alerting coverage on the same routine you’d use for anything else, don’t wait for a symptom to reveal that automation quietly stopped.
- Review generated maintenance DDL before running it, that reviewability is the entire point of building it this way instead of installing an opaque framework.
- Run an index health pass on its own cadence even with maintenance jobs in place, an index rebuild step tells you it ran, not whether the index picture underneath actually needed it.
- Treat “no failed jobs” and “jobs are actually accomplishing something” as two different questions, the first is what SQL Agent and Jobs checks, the second needs Index Maintenance or a direct look at what each job produced.
Common Questions
A job is enabled and shows no failures. Is that enough?
No. A job can be enabled, never fail, and never run, because its schedule was disabled or deleted separately from the job itself. Enabled and scheduled are two different states and only one of them does any work.
How often should DBCC CHECKDB run?
Weekly is a reasonable default for most systems, more often for anything where corruption would be expensive to discover late. The point is knowing when it last ran successfully, which is a question a surprising number of estates cannot answer. DBCC CHECKDB Found Corruption, in Related Scripts below, covers that in full.
Should I rebuild or reorganise indexes?
Follow fragmentation and size rather than a blanket rule: reorganise moderate fragmentation, rebuild heavy fragmentation, and leave small tables alone entirely. Rebuilding everything nightly costs I/O and log without buying much.
Do maintenance jobs need their own failure alerting?
Yes, and it should be separate from the job’s own history. If the alerting depends on the same Agent that stopped running, the silence looks identical to success.
See Also
- DBA Scripts: The Complete Guide, the full pillar index
- Server and Configuration
- Performance and Troubleshooting
Summary
SQL Agent and Jobs confirms the automation layer itself is healthy, Index Maintenance covers the most common thing that quietly needs it, and Maintenance Job Framework is how the automation gets built in a form you can actually review. Fourteen scripts, one underlying concern: not just whether jobs are running, but whether they’re doing the job.
Leave a Reply