DBA Scripts: Maintenance and Automation

Part of the DBA-Tools Project.

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.

The Three Pillars

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_jobstep DDL for backups, integrity checks, and index maintenance, reviewable line by line before it ever runs.

How They Fit Together

1. 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.
2. 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.
3. 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.

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.