DBA Scripts: SQL Server Maintenance Job Framework

Part of the DBA-Tools Project.

If you’re already running Ola Hallengren’s maintenance solution, this framework won’t replace it, but the approach might still be useful: three small, readable T-SQL generator scripts that build the same category of scheduled job framework, full and log backups, backup cleanup, DBCC CHECKDB, index and statistics maintenance, job/backup history cleanup, and error log cycling, as plain sp_add_job / sp_add_jobstep DDL you can review end to end before you ever run it.

That readability is the point. Every job step is copy/paste T-SQL or PowerShell you can review line by line, not a call into a larger set of procedures. Eight jobs come out of three generator scripts, each covered on its own page below, tested to destruction against a real SQL Server 2025 instance while writing this series. Three real findings turned up doing that, two genuine script bugs and one resource-scheduling lesson, each documented on the page for the job it affects.

The Framework, Grouped by What Each Generator Builds

Why a Generated, Readable Framework Matters

• A maintenance framework you can’t read is a maintenance framework you can’t fully trust, and most DBAs run someone else’s maintenance procedures without ever fully reading them
• Generator scripts separate “decide the parameters” from “run the DDL”: you review the exact sp_add_job calls before anything touches msdb
• Every job step is copy/paste T-SQL or PowerShell, not a black-box procedure call, when something needs troubleshooting during an incident, you’re reading the actual command, not stepping into unfamiliar procedure code
• Idempotent generation: every job starts with IF EXISTS ... sp_delete_job before recreating it, so re-running a generator after changing a parameter cleanly replaces the old job rather than leaving duplicates behind

How They Fit Together

A sensible deployment order for a new instance:

1. Start with Generate Backup Jobs, nothing else matters if backups aren’t running
2. Add Generate Database Integrity and Housekeeping Jobs next, DBCC CHECKDB coverage and msdb growth control
3. Finish with Generate Index Maintenance Jobs, the heaviest job by resource cost, deploy once the other two are confirmed stable
4. Confirm every job keeps succeeding with Get Maintenance Job Status, a scheduled job that silently stops succeeding is worse than no job at all

Best Practices Across the Series

• Review the generated DDL before running it. Every script only produces text; nothing touches msdb until you execute the output yourself.
• Regenerate rather than hand-edit: change a parameter and re-run, the IF EXISTS ... sp_delete_job pattern replaces cleanly rather than duplicating.
• Don’t schedule DBA - Integrity Check or DBA - Index Maintenance to overlap with backups or each other on an instance under memory pressure, both jobs assume they’ll get the memory grant they ask for, and that assumption fails loudly on a shared or resource-constrained box.
• After any maintenance job fails with an error cascade mentioning 9001, 3314, or 3908, check sys.databases.state_desc for that database before assuming corruption, SQL Server’s own crash recovery is usually the actual story.
• Confirm job success periodically with Get Maintenance Job Status, not just on the day you first deployed.

See Also

This pillar is part of DBA Scripts: The Complete Guide, the map across the whole series organized by the question you’re actually asking.

Summary

Two genuine script bugs turned up testing this framework against a real instance, an EXEC parameter that can’t be a function call, and a CmdExec subsystem that doesn’t wrap commands in a shell, both fixed and verified. A third finding wasn’t a script bug at all: on a memory-constrained instance, DBCC CHECKDB can queue forever on a RESOURCE_SEMAPHORE wait while index maintenance fails loudly mid-rebuild, several different ways “the DDL looks right” and “the job actually works” can diverge. That’s the case for testing a maintenance framework, generated or otherwise, against a real instance before trusting it. Start with backups, add integrity and housekeeping, finish with index maintenance, and check back with Get Maintenance Job Status to confirm they keep succeeding on schedule, not just on day one.