DBA Scripts: SQL Server Maintenance Job Framework

---

If you’re already running Ola Hallengren’s maintenance solution, this framework won’t replace it, but the approach might still be useful: 3 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. 8 jobs come out of 3 generator scripts, each covered on its own page below.

---

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

---

Start Here

---

The Framework, Grouped by What Each Generator Builds

---

How They Fit Together

A sensible deployment order for a new instance:

---

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.

---

Frequently Asked Questions

---

Summary

3 generator scripts, 8 readable jobs: 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. What each job does, and what to watch for when it runs, is on that job’s own page.

---

Related