Part of the DBA-Tools Project.
Building a Full SQL Server Maintenance Job Framework You Can Actually Read
If you’re already running Ola Hallengren’s maintenance solution, this won’t replace it, but the approach here 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, error log cycling) as plain sp_add_job / sp_add_jobstep DDL you can read 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. What it does claim is that you should be able to understand every job it creates, and that’s exactly what got tested to destruction against a real SQL Server 2025 instance while writing this post.
Three real bugs turned up doing that. All three are fixed below, in the scripts and in this post.
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_jobcalls the script is about to run, in your own SSMS window, before anything touchesmsdb. - Every job step is copy/paste T-SQL, not a black-box procedure call — when something needs troubleshooting during an incident, you’re reading
ALTER INDEX ... REBUILD, not stepping into unfamiliar procedure code. - Idempotent generation: every job the scripts create starts with
IF EXISTS ... sp_delete_jobbefore recreating it, so re-running a generator after changing a parameter cleanly replaces the old job rather than leaving duplicates behind.
When to Run These Scripts
- Standing up a maintenance framework on a new instance that has none
- Replacing an ad-hoc set of maintenance jobs with a documented, regenerable one
- Auditing what an existing “DBA -” job framework actually does, by reading the generator that produced it
- Any time a maintenance parameter (retention days, thresholds, schedule) needs to change, edit the parameter block and regenerate, rather than hand-editing job steps in SSMS
The Three Scripts
Each script has one job: generate DDL text (SELECT @ddl), which you review, then run against the target instance. None of them touch msdb themselves, that’s a deliberate separation between “decide what the jobs should look like” and “actually create them.”
Generate-BackupJobs.sql — Full backups, log backups, cleanup
Creates DBA - Backup - FULL (daily full backup of every online, writable user database), DBA - Backup - LOG (log backups on a short interval, skipping SIMPLE-recovery databases automatically), and DBA - Backup - Cleanup (deletes backup files past retention).
/*
Script Name : Generate-BackupJobs
Category : maintenance
Purpose : Generates SQL Agent DDL to create three scheduled maintenance jobs:
DBA - Backup - FULL daily full backup of all online user databases
DBA - Backup - LOG transaction log backups on a short interval (default 15 min)
DBA - Backup - Cleanup removes old backup files based on retention policy
Edit the parameters section, review the output, then run on the target instance.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-generate-maintenance-jobs/)
Requires : VIEW ANY DATABASE
Notes : Output requires sysadmin or SQLAgentOperatorRole on the target instance.
The backup root path must exist on the SQL Server host before jobs first run.
Log backup job skips SIMPLE recovery model databases automatically.
Cleanup step uses the PowerShell subsystem — SQL Agent service account needs
delete rights on the backup folder.
On AGs: use @FullBackupPreference to avoid full backups on the wrong replica;
this script backs up whatever instance it runs on.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @BackupRootPath nvarchar(260) = N'D:\SQLBackups';
DECLARE @FullRetentionDays int = 14; -- full backup files older than this are deleted
DECLARE @LogRetentionHours int = 48; -- log backup files older than this are deleted
DECLARE @FullScheduleHour tinyint = 2; -- 0-23 — hour for the daily full backup
DECLARE @LogIntervalMins tinyint = 15; -- log backup frequency in minutes
DECLARE @JobOwner sysname = N'sa';
DECLARE @CategoryName nvarchar(128) = N'Database Maintenance';
-- ─────────────────────────────────────────────────────────────────────────────
(The full script, including the DDL-assembly section, is in the repo, link below.)
Generate-MaintenanceJobs.sql — Integrity checks and housekeeping
Creates DBA - Integrity Check (weekly DBCC CHECKDB across every online user database), DBA - History Cleanup (purges msdb backup history, job history, and Database Mail log past retention), and DBA - Cycle Error Log (sp_cycle_errorlog to keep the error log from growing unbounded).
/*
Script Name : Generate-MaintenanceJobs
Category : maintenance
Purpose : Generates SQL Agent DDL for routine housekeeping jobs:
DBA - Integrity Check DBCC CHECKDB on all online user databases (weekly)
DBA - History Cleanup purges msdb backup history, job history, and
Database Mail log based on retention periods (weekly)
DBA - Cycle Error Log sp_cycle_errorlog to rotate the SQL Server error log
and prevent it growing unbounded (weekly)
Edit the parameters section, review the output, then run on the target instance.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-generate-maintenance-jobs/)
Requires : VIEW ANY DATABASE
Notes : DBCC CHECKDB is resource-intensive. Schedule on a quiet period.
On a large estate, consider reducing to monthly or running per-filegroup.
History Cleanup deletes msdb rows permanently — retention periods are minimums.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @BackupHistoryDays int = 90; -- backup/restore history kept this many days
DECLARE @JobHistoryDays int = 30; -- job history records kept this many days
DECLARE @CheckDbHour tinyint = 2; -- hour (0-23) for weekly integrity check
DECLARE @CleanupHour tinyint = 3; -- hour (0-23) for weekly history cleanup
DECLARE @CycleLogHour tinyint = 0; -- hour (0-23) for weekly error log cycle
DECLARE @JobOwner sysname = N'sa';
DECLARE @CategoryName nvarchar(128) = N'Database Maintenance';
-- ─────────────────────────────────────────────────────────────────────────────
The history cleanup step, after the fix described below:
DECLARE @cleanCmd nvarchar(max) = REPLACE(
N'SET NOCOUNT ON;
DECLARE @BackupCutoff datetime = DATEADD(DAY, -<<BACKUP_DAYS>>, GETDATE());
DECLARE @JobCutoff datetime = DATEADD(DAY, -<<JOB_DAYS>>, GETDATE());
-- Purge backup and restore history
EXEC msdb.dbo.sp_delete_backuphistory
@oldest_date = @BackupCutoff;
-- Purge SQL Agent job history
EXEC msdb.dbo.sp_purge_jobhistory
@oldest_date = @JobCutoff;
-- Purge Database Mail items and log (only if Database Mail is configured)
IF EXISTS (SELECT 1 FROM msdb.dbo.sysmail_profile)
BEGIN
EXEC msdb.dbo.sysmail_delete_mailitems_sp
@sent_before = @JobCutoff;
EXEC msdb.dbo.sysmail_delete_log_sp
@logged_before = @JobCutoff;
END'
, N'|', NCHAR(39));
Generate-IndexMaintenanceJobs.sql — Index rebuilds/reorganizes and statistics
Creates DBA - Index Maintenance (rebuilds or reorganizes fragmented indexes based on configurable thresholds, using sys.dm_db_index_physical_stats with a LIMITED scan, automatically detecting ONLINE = ON support via SERVERPROPERTY('EngineEdition')) and DBA - Statistics Update (sp_updatestats per database).
/*
Script Name : Generate-IndexMaintenanceJobs
Category : maintenance
Purpose : Generates SQL Agent DDL for:
DBA - Index Maintenance rebuilds/reorganizes fragmented indexes across
all online user databases using LIMITED scan.
Automatically uses ONLINE = ON on Enterprise/Developer;
falls back to offline rebuild on Standard/Web edition.
DBA - Statistics Update runs sp_updatestats on every online user database
(tables that had rows modified since last update only).
Edit the parameters section, review the output, then run on the target instance.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-generate-maintenance-jobs/)
Requires : VIEW ANY DATABASE, VIEW DATABASE STATE
Notes : Index maintenance runtime varies widely with database count and size.
Schedule outside peak hours. On a busy 3 000-database estate, consider
splitting the job across multiple days or server groups.
Online rebuild requires Enterprise or Developer edition — detected at job runtime.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @FragReorgThreshold decimal(5,1) = 10.0; -- frag% >= this: REORGANIZE
DECLARE @FragRebuildThreshold decimal(5,1) = 30.0; -- frag% >= this: REBUILD (overrides reorg)
DECLARE @MinPageCount int = 1000; -- skip indexes smaller than this
DECLARE @MaintScheduleHour tinyint = 1; -- hour (0-23) for weekly index job
DECLARE @StatsScheduleHour tinyint = 23; -- hour (0-23) for weekly stats job
DECLARE @JobOwner sysname = N'sa';
DECLARE @CategoryName nvarchar(128)= N'Database Maintenance';
-- ─────────────────────────────────────────────────────────────────────────────
How To Run From The Repo
Clone DBA Tools, initialize and run each generator, review the DDL it produces, then execute it against the target instance:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Generate each framework's DDL — review the output before running it:
.\run.ps1 Generate-BackupJobs
.\run.ps1 Generate-MaintenanceJobs
.\run.ps1 Generate-IndexMaintenanceJobs
# To generate against a remote server:
.\run.ps1 Generate-BackupJobs -ServerInstance SQLSERVER01
These scripts live in the repo at:
sql/maintenance/Generate-BackupJobs.sqlsql/maintenance/Generate-MaintenanceJobs.sqlsql/maintenance/Generate-IndexMaintenanceJobs.sql
Three Real Bugs Found Testing This Against SQL Server 2025
Every generator produces DDL that looks correct on read-through. Two of the three job frameworks failed the first time their generated jobs actually ran, against a real local SQL Server 2025 instance. Here’s what actually broke, and how it got fixed. A maintenance framework post that doesn’t test its own job steps isn’t earning the trust it’s asking for.
Bug 1 — EXEC parameters can’t be function-call expressions
DBA - History Cleanup failed immediately with Msg 102, Level 15: Incorrect syntax near 'DAY'. The original step called:
EXEC msdb.dbo.sp_delete_backuphistory
@oldest_date = DATEADD(DAY, -90, GETDATE());
T-SQL’s EXEC statement only accepts constants or variables as named parameter values, not function-call expressions. DATEADD(...) inline as a parameter fails. The fix, shown in the script above, is to assign the computed cutoff to a local variable first, then pass the variable. Small rule, easy to trip on, and it would have silently broken history cleanup on every instance this framework was deployed to.
Bug 2 — SQL Agent’s CmdExec subsystem does not wrap your command in a shell
DBA - Backup - Cleanup (originally built on forfiles.exe via the CmdExec subsystem) took three attempts to actually fix, because each fix revealed a deeper layer of the same misconception:
- First failure:
forfiles: Invalid argument/option - '2>nul'. The command tried to redirectforfiles‘ “nothing matched” noise with2>nul, assuming CmdExec runs inside a shell that interprets redirection. It doesn’t,2>nulgets passed toforfiles.exeas a literal argument. - Second failure, after removing the redirection:
No files found with the specified search criteria. Process Exit Code 1.A trailingexit 0on its own line was meant to maskforfiles‘ own benign non-zero exit code when nothing matched, but CmdExec only ever runs the first line of a multi-line command string, so theexit 0never executed at all. - Third failure, after chaining everything onto one line with
&:forfiles: Invalid argument/option - '&'. Same root cause as the first failure. CmdExec passes the command directly toCreateProcess, with no shell in between, so&chaining is just as invalid as2>nulredirection.
The actual fix wasn’t another layer of shell-escaping, it was recognizing that forfiles + CmdExec was the wrong tool for a multi-step cleanup command. The rewritten step uses SQL Agent’s PowerShell subsystem instead, which runs the command text as a real PowerShell script rather than a raw process invocation, so multi-line commands and pipes just work:
DECLARE @cleanCmd nvarchar(max) =
N'Get-ChildItem -Path ' + @q + @BackupRootPath + @q + N' -Filter ' + @q + N'*_FULL_*.bak' + @q
+ N' -ErrorAction SilentlyContinue | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-'
+ CAST(@FullRetentionDays AS nvarchar(5)) + N') } | Remove-Item -Force' + @crlf
+ N'Get-ChildItem -Path ' + @q + @BackupRootPath + @q + N' -Filter ' + @q + N'*_LOG_*.trn' + @q
+ N' -ErrorAction SilentlyContinue | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-'
+ CAST(@logRetentionDays AS nvarchar(5)) + N') } | Remove-Item -Force';
Confirmed against the real job history after the fix:
job_name : DBA - Backup - Cleanup
run_status : 1 (Succeeded)
message : Executed as user: NT Service\SQLSERVERAGENT.
The step did not generate any output. Process Exit Code 0.
The step succeeded.
Bug 3 (not a script bug) — memory pressure makes index maintenance and integrity checks fail in ways that look worse than they are
Not a bug in the generator scripts, but a real, reproducible finding from running this framework’s heavier jobs against a resource-constrained instance, worth building into how any of this gets scheduled. Two separate incidents on the same test instance:
DBA - Integrity Check, run against every database on the instance concurrently with other load, sat at percent_complete = 0 for over 25 minutes on DBCC CHECKDB, waiting on RESOURCE_SEMAPHORE (a memory grant it couldn’t get), with estimated_completion_time = 0. That’s indistinguishable from the outside between “still starting up” and “will never get the memory it needs.” The pressure was severe enough that brand-new connection attempts to the instance failed at the pre-login handshake stage entirely:
Connection Timeout Expired. The timeout period elapsed while attempting to
consume the pre-login handshake acknowledgement. [Pre-Login] initialization=93; handshake=60096;
DBA - Index Maintenance failed outright under the same kind of pressure, part-way through a rebuild against a real database, with a genuinely alarming error cascade in the job history: Error 802 (insufficient buffer pool memory), Error 9001 (“the log for database … is not available”), Error 3314 (failure during undo of a logged operation), Error 845 (buffer latch timeout), and Error 3908 (“database is in emergency mode … must be restarted”). Read in isolation, that looks like real corruption. Checking sys.databases immediately after showed the database back to ONLINE with no flags set. SQL Server’s own recovery handled it.
Chasing down why led to the actual root cause: sys.configurations showed max server memory capped at 1800 MB, and sys.dm_os_sys_memory showed the host itself down to under 200 MB of free physical memory out of 8 GB total, system_memory_state_desc = 'Available physical memory is running low'. This wasn’t a dedicated SQL Server box under a heavy maintenance schedule, it was a development machine also running Docker Desktop, a WSL2 VM, an IDE, and other applications competing for the same RAM. max server memory at 1800 MB was actually a sensible, conservative setting for that box. A follow-up DBCC CHECKDB against the affected database, meant to confirm no lasting page-level damage, hit the same RESOURCE_SEMAPHORE wait repeatedly and never completed while this post was being written. The honest status is “online and structurally reporting healthy, full integrity check not yet confirmed,” not “confirmed clean,” and that gap is being left visible here rather than papered over.
The practical takeaway: neither job is broken, but both assume they’ll get the memory they ask for. On a shared or resource-constrained box, that assumption fails loudly. A DBCC CHECKDB that can’t get a grant just sits at zero forever, while an index rebuild that loses its memory grant mid-transaction can throw a wall of errors that reads far worse than what’s actually wrong. Before scheduling DBA - Integrity Check or DBA - Index Maintenance, check what else is actually competing for memory on that host, not just whether the clock says off-peak. On a genuinely shared machine, sys.dm_os_sys_memory and sys.configurations (max server memory (MB)) are worth checking before blaming the job. When a maintenance job throws a scary error cascade under load, check sys.databases.state_desc before assuming corruption, SQL Server’s own crash recovery is usually the actual story.
Example Output — Verified Job Runs
Real results from msdb.dbo.sysjobhistory after applying the fixes above, run against a local SQL Server 2025 instance:
| Job | run_status | Duration | Message |
|---|---|---|---|
DBA - History Cleanup |
Succeeded | <1s | 0 history entries purged. (Message 14226) |
DBA - Backup - Cleanup |
Succeeded | 4-5s | Process Exit Code 0. The step succeeded. |
DBA - Statistics Update |
Succeeded | 59s | 0 index(es)/statistic(s) have been updated, 2 did not require update. (Message 15651) |
DBA - Index Maintenance |
Failed (under memory pressure) | 13m 13s | Error cascade (802 / 9001 / 3314 / 845 / 3908) — database confirmed back ONLINE, see Bug 3 |
Understanding the Results
run_status = 1insysjobhistorymeans the step succeeded, check this before trusting a job’s schedule to mean it’s actually working, exactly the gap covered in Get Maintenance Job Status.- A near-zero duration on History Cleanup or Statistics Update is normal on a lab-sized instance.
sp_delete_backuphistoryandsp_updatestatsonly touch rows past their retention window or tables with modified rows since the last update; an empty or quiet instance has little to do. - The Backup Cleanup job’s success message being generic (
Process Exit Code 0) is expected for the PowerShell subsystem when nothing is deleted.Remove-Itemdoesn’t report a count of what it removed unless asked to. - A stuck
percent_complete = 0on DBCC CHECKDB, or an index maintenance job that fails with a scary error cascade under load, is a resource-scheduling problem, not a script bug — see Bug 3 above. Checksys.databases.state_descfor the affected database before assuming damage.
Best Practices
- Review the generated DDL before running it. Every one of these scripts only produces text; nothing touches
msdbuntil you execute the output yourself. - Regenerate rather than hand-edit: change a parameter at the top of the script and re-run it. The
IF EXISTS ... sp_delete_jobpattern means the old job is cleanly replaced, not duplicated. - Don’t schedule
DBA - Integrity CheckorDBA - Index Maintenanceto overlap with backups or each other on an instance under memory pressure. Bug 3 above is what that overlap actually looks like, and index maintenance failing mid-rebuild is a worse position to be in than CHECKDB simply queuing. - After any maintenance job fails with an error cascade mentioning
9001,3314, or3908, checksys.databases.state_descfor that database immediately. Don’t assume corruption until you’ve ruled out a resource-starved rollback that SQL Server already recovered from on its own. - Confirm job success with Get Maintenance Job Status after first deploying this framework, and periodically afterward. A scheduled job that silently stops succeeding is worse than no job at all, because it looks like coverage that isn’t there.
Related Scripts
You may also find these scripts useful:
- Get Maintenance Job Status
- Index Maintenance Script
- SQL Agent Job Overview
- SQL Agent Job Failure Summary
Summary
None of these issues were visible from reading the generated DDL, every one of them only showed up when the generated jobs actually ran against a real instance. An EXEC parameter that can’t be a function call, a CmdExec subsystem that doesn’t wrap commands in a shell, and a memory-starved instance where DBCC CHECKDB just queues forever 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 treating a maintenance framework, generated or otherwise, as something to test against a real instance before trusting it, not just something to read once and deploy. The two genuine script bugs are fixed and verified. The resource-pressure findings aren’t bugs to fix in the scripts themselves. They’re the reason DBA - Integrity Check and DBA - Index Maintenance need real breathing room in your schedule, not just an off-peak timestamp. Regenerate, review, and run these scripts, and check back with Get Maintenance Job Status to confirm they keep succeeding on schedule, not just on day one.
Leave a Reply