Part of the DBA-Tools Project.
A Readable Generator for Integrity Checks and msdb Housekeeping
If you’re already running Ola Hallengren’s maintenance solution, this won’t replace it, but the approach here might still be useful: a small, readable T-SQL generator that builds a housekeeping job framework as plain sp_add_job / sp_add_jobstep DDL you can review line by line before you ever run it.
This script generates DDL for three jobs: 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). It only produces text, nothing touches msdb until you review the output and run it yourself.
Why a Generated, Readable Housekeeping Framework Matters
- A framework you can’t read is one you can’t fully trust, and most DBAs run someone else’s housekeeping jobs without ever fully reading what they do
- The generator separates “decide the parameters” from “run the DDL”: you review the exact
sp_add_jobcalls 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 mid-incident, you’re reading
DBCC CHECKDBorsp_delete_backuphistorydirectly, not stepping into unfamiliar code - Idempotent generation: the script starts with
IF EXISTS ... sp_delete_jobbefore recreating each job, so re-running after changing a parameter cleanly replaces the old job rather than leaving duplicates
When to Run This Script
- Standing up integrity checks and
msdbhousekeeping on a new instance that has none - Replacing an ad-hoc set of maintenance jobs with a documented, regenerable one
msdbgrowing unexpectedly large, stale backup or job history that was never being purged is a common cause- Any time a retention parameter needs to change, edit the parameter block and regenerate rather than hand-editing job steps in SSMS
The Script
/*
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-database-integrity-and-housekeeping-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));
(The full script, including the DDL-assembly section, is in the repo, link below.)
How To Run From The Repo
Clone DBA Tools, initialize and run the 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 the DDL — review the output before running it:
.\run.ps1 Generate-MaintenanceJobs
# To generate against a remote server:
.\run.ps1 Generate-MaintenanceJobs -ServerInstance SQLSERVER01
This script lives in the repo at:
Two Real Findings Testing This Against SQL Server 2025
Every generator produces DDL that looks correct on read-through. Testing it against a real local SQL Server 2025 instance turned up one genuine script bug and one resource-scheduling finding that isn’t a bug but matters just as much for how this gets deployed.
Bug — 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.
Not a bug — memory pressure makes DBCC CHECKDB look stuck rather than slow
A real, reproducible finding from running DBA - Integrity Check against every database on a resource-constrained instance concurrently with other load: it sat at percent_complete = 0 for over 25 minutes, 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;
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.
Example Output — Verified Job Runs
Real results from msdb.dbo.sysjobhistory after applying the fix 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 - Integrity Check‘s own duration isn’t included here honestly, it was still queued on RESOURCE_SEMAPHORE when this post was written, the memory-pressure finding above, not a job failure.
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 is normal on a lab-sized instance.
sp_delete_backuphistoryonly touches rows past their retention window; an empty or quiet instance has little to do - A stuck
percent_complete = 0on DBCC CHECKDB is a resource-scheduling problem, not a script bug. Checksys.dm_os_sys_memoryandsys.configurations(max server memory (MB)) before assuming the job itself is broken
Best Practices
- Review the generated DDL before running it. The script 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. - Never pass a function call directly as an
EXECnamed parameter, assign it to a variable first. - Don’t schedule
DBA - Integrity Checkto overlap with backups or index maintenance on an instance under memory pressure, a stuckRESOURCE_SEMAPHOREwait can look indistinguishable from a hang. - Confirm job success with Get Maintenance Job Status after first deploying this framework, and periodically afterward.
Related Scripts
You may also find these scripts useful:
- SQL Server Maintenance Job Framework (hub)
- Generate Backup Jobs
- Generate Index Maintenance Jobs
- Get Maintenance Job Status
- Get Last DBCC CHECKDB
Frequently Asked Questions
Why did EXEC ... @oldest_date = DATEADD(...) fail?
T-SQL’s EXEC statement only accepts constants or variables for named parameters, not inline function-call expressions. Compute the value into a local variable first (DECLARE @Cutoff datetime = DATEADD(...)), then pass @Cutoff.
DBCC CHECKDB seems stuck at 0%, is it hung?
Check sys.dm_exec_requests for the session’s wait_type. A RESOURCE_SEMAPHORE wait means it’s queued for a memory grant it hasn’t received yet, not hung, it will proceed once memory frees up. Check sys.dm_os_sys_memory for actual free physical memory on the host before assuming a deeper problem.
Summary
DBA - Integrity Check, DBA - History Cleanup, and DBA - Cycle Error Log come out of one generator script you can read end to end before running. The one real bug found testing this against a live instance, an EXEC parameter that can’t be a function call, is fixed and verified. The memory-pressure finding on DBCC CHECKDB isn’t a script bug, it’s the reason this job needs real breathing room in your schedule, not just an off-peak timestamp. Regenerate, review, and run, then confirm ongoing success with Get Maintenance Job Status.
Leave a Reply