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';
-- ─────────────────────────────────────────────────────────────────────────────
DECLARE @q nchar(1) = NCHAR(39);
DECLARE @crlf nvarchar(2) = CHAR(13) + CHAR(10);
DECLARE @ddl nvarchar(max) = N'';
DECLARE @checkSchedTS int = @CheckDbHour * 10000;
DECLARE @cleanSchedTS int = @CleanupHour * 10000;
DECLARE @cycleSchedTS int = @CycleLogHour * 10000;
-- ── Step command: DBCC CHECKDB ────────────────────────────────────────────────
DECLARE @checkCmd nvarchar(max) = REPLACE(
N'SET NOCOUNT ON;
DECLARE @db sysname, @sql nvarchar(max);
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE database_id > 4 AND state_desc = N|ONLINE| AND is_read_only = 0
ORDER BY name;
OPEN c;
FETCH NEXT FROM c INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N|DBCC CHECKDB ([| + @db + N|]) WITH NO_INFOMSGS, ALL_ERRORMSGS;|;
EXEC sp_executesql @sql;
FETCH NEXT FROM c INTO @db;
END
CLOSE c;
DEALLOCATE c;'
, N'|', NCHAR(39));
-- ── Step command: history cleanup ─────────────────────────────────────────────
-- EXEC statement parameters must be constants or variables, not function-call
-- expressions — DATEADD(...) inline as a named parameter value fails with
-- "Incorrect syntax near 'DAY'". Assign to a local variable first.
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));
SET @cleanCmd = REPLACE(@cleanCmd, N'<<BACKUP_DAYS>>', CAST(@BackupHistoryDays AS nvarchar(5)));
SET @cleanCmd = REPLACE(@cleanCmd, N'<<JOB_DAYS>>', CAST(@JobHistoryDays AS nvarchar(5)));
-- ── Step command: cycle error log ────────────────────────────────────────────
DECLARE @cycleCmd nvarchar(max) = N'EXEC sp_cycle_errorlog;';
-- ═══════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════
SET @ddl =
N'-- =================================================================' + @crlf +
N'-- Generated by Generate-MaintenanceJobs.sql' + @crlf +
N'-- Server : ' + @@SERVERNAME + @crlf +
N'-- Backup history kept : ' + CAST(@BackupHistoryDays AS nvarchar(5)) + N' days' + @crlf +
N'-- Job history kept : ' + CAST(@JobHistoryDays AS nvarchar(5)) + N' days' + @crlf +
N'-- Generated : ' + CONVERT(nvarchar(20), GETDATE(), 120) + @crlf +
N'-- =================================================================' + @crlf +
@crlf +
N'USE msdb;' + @crlf +
N'GO' + @crlf;
-- Category
SET @ddl +=
@crlf +
N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.syscategories' + @crlf +
N' WHERE name = N' + @q + @CategoryName + @q + N' AND category_class = 1)' + @crlf +
N' EXEC msdb.dbo.sp_add_category' + @crlf +
N' @class = N' + @q + N'JOB' + @q + N', @type = N' + @q + N'LOCAL' + @q
+ N', @name = N' + @q + @CategoryName + @q + N';' + @crlf +
N'GO' + @crlf;
-- ── Job 1: DBA - Integrity Check ──────────────────────────────────────────────
SET @ddl +=
@crlf +
N'-- ==================================================================' + @crlf +
N'-- Job: DBA - Integrity Check' + @crlf +
N'-- Schedule: weekly, Saturday at ' + CAST(@CheckDbHour AS nvarchar(2)) + N':00' + @crlf +
N'-- ==================================================================' + @crlf +
N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + N'DBA - Integrity Check' + @q + N')' + @crlf +
N' EXEC msdb.dbo.sp_delete_job' + @crlf +
N' @job_name = N' + @q + N'DBA - Integrity Check' + @q + N',' + @crlf +
N' @delete_unused_schedule = 1;' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_add_job' + @crlf +
N' @job_name = N' + @q + N'DBA - Integrity Check' + @q + N',' + @crlf +
N' @enabled = 1,' + @crlf +
N' @owner_login_name = N' + @q + @JobOwner + @q + N',' + @crlf +
N' @category_name = N' + @q + @CategoryName + @q + N';' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_add_jobstep' + @crlf +
N' @job_name = N' + @q + N'DBA - Integrity Check' + @q + N',' + @crlf +
N' @step_id = 1,' + @crlf +
N' @step_name = N' + @q + N'DBCC CHECKDB all user databases' + @q + N',' + @crlf +
N' @subsystem = N' + @q + N'TSQL' + @q + N',' + @crlf +
N' @database_name = N' + @q + N'master' + @q + N',' + @crlf +
N' @command = N' + @q + REPLACE(@checkCmd, @q, @q + @q) + @q + N',' + @crlf +
N' @retry_attempts = 0,' + @crlf +
N' @on_success_action = 1,' + @crlf +
N' @on_fail_action = 2;' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_add_schedule' + @crlf +
N' @schedule_name = N' + @q + N'DBA - Integrity Check Weekly Sat '
+ CAST(@CheckDbHour AS nvarchar(2)) + N':00' + @q + N',' + @crlf +
N' @freq_type = 8,' + @crlf +
N' @freq_interval = 64,' + @crlf + -- 64 = Saturday
N' @freq_recurrence_factor = 1,' + @crlf +
N' @active_start_time = ' + CAST(@checkSchedTS AS nvarchar(10)) + N';' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_attach_schedule' + @crlf +
N' @job_name = N' + @q + N'DBA - Integrity Check' + @q + N',' + @crlf +
N' @schedule_name = N' + @q + N'DBA - Integrity Check Weekly Sat '
+ CAST(@CheckDbHour AS nvarchar(2)) + N':00' + @q + N';' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + N'DBA - Integrity Check' + @q + N';' + @crlf +
N'GO' + @crlf;
-- ── Job 2: DBA - History Cleanup ──────────────────────────────────────────────
SET @ddl +=
@crlf +
N'-- ==================================================================' + @crlf +
N'-- Job: DBA - History Cleanup' + @crlf +
N'-- Schedule: weekly, Sunday at ' + CAST(@CleanupHour AS nvarchar(2)) + N':00' + @crlf +
N'-- ==================================================================' + @crlf +
N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + N'DBA - History Cleanup' + @q + N')' + @crlf +
N' EXEC msdb.dbo.sp_delete_job' + @crlf +
N' @job_name = N' + @q + N'DBA - History Cleanup' + @q + N',' + @crlf +
N' @delete_unused_schedule = 1;' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_add_job' + @crlf +
N' @job_name = N' + @q + N'DBA - History Cleanup' + @q + N',' + @crlf +
N' @enabled = 1,' + @crlf +
N' @owner_login_name = N' + @q + @JobOwner + @q + N',' + @crlf +
N' @category_name = N' + @q + @CategoryName + @q + N';' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_add_jobstep' + @crlf +
N' @job_name = N' + @q + N'DBA - History Cleanup' + @q + N',' + @crlf +
N' @step_id = 1,' + @crlf +
N' @step_name = N' + @q + N'Purge backup history, job history, and mail log' + @q + N',' + @crlf +
N' @subsystem = N' + @q + N'TSQL' + @q + N',' + @crlf +
N' @database_name = N' + @q + N'msdb' + @q + N',' + @crlf +
N' @command = N' + @q + REPLACE(@cleanCmd, @q, @q + @q) + @q + N',' + @crlf +
N' @retry_attempts = 0,' + @crlf +
N' @on_success_action = 1,' + @crlf +
N' @on_fail_action = 2;' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_add_schedule' + @crlf +
N' @schedule_name = N' + @q + N'DBA - History Cleanup Weekly Sun '
+ CAST(@CleanupHour AS nvarchar(2)) + N':00' + @q + N',' + @crlf +
N' @freq_type = 8,' + @crlf +
N' @freq_interval = 1,' + @crlf + -- 1 = Sunday
N' @freq_recurrence_factor = 1,' + @crlf +
N' @active_start_time = ' + CAST(@cleanSchedTS AS nvarchar(10)) + N';' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_attach_schedule' + @crlf +
N' @job_name = N' + @q + N'DBA - History Cleanup' + @q + N',' + @crlf +
N' @schedule_name = N' + @q + N'DBA - History Cleanup Weekly Sun '
+ CAST(@CleanupHour AS nvarchar(2)) + N':00' + @q + N';' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + N'DBA - History Cleanup' + @q + N';' + @crlf +
N'GO' + @crlf;
-- ── Job 3: DBA - Cycle Error Log ──────────────────────────────────────────────
SET @ddl +=
@crlf +
N'-- ==================================================================' + @crlf +
N'-- Job: DBA - Cycle Error Log' + @crlf +
N'-- Schedule: weekly, Monday at ' + CAST(@CycleLogHour AS nvarchar(2)) + N':00' + @crlf +
N'-- Rotates the SQL Server error log; keeps last 6 archived logs by default.' + @crlf +
N'-- To increase archived log count: HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs' + @crlf +
N'-- ==================================================================' + @crlf +
N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + N'DBA - Cycle Error Log' + @q + N')' + @crlf +
N' EXEC msdb.dbo.sp_delete_job' + @crlf +
N' @job_name = N' + @q + N'DBA - Cycle Error Log' + @q + N',' + @crlf +
N' @delete_unused_schedule = 1;' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_add_job' + @crlf +
N' @job_name = N' + @q + N'DBA - Cycle Error Log' + @q + N',' + @crlf +
N' @enabled = 1,' + @crlf +
N' @owner_login_name = N' + @q + @JobOwner + @q + N',' + @crlf +
N' @category_name = N' + @q + @CategoryName + @q + N';' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_add_jobstep' + @crlf +
N' @job_name = N' + @q + N'DBA - Cycle Error Log' + @q + N',' + @crlf +
N' @step_id = 1,' + @crlf +
N' @step_name = N' + @q + N'Cycle error log' + @q + N',' + @crlf +
N' @subsystem = N' + @q + N'TSQL' + @q + N',' + @crlf +
N' @database_name = N' + @q + N'master' + @q + N',' + @crlf +
N' @command = N' + @q + REPLACE(@cycleCmd, @q, @q + @q) + @q + N',' + @crlf +
N' @retry_attempts = 0,' + @crlf +
N' @on_success_action = 1,' + @crlf +
N' @on_fail_action = 2;' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_add_schedule' + @crlf +
N' @schedule_name = N' + @q + N'DBA - Cycle Error Log Weekly Mon '
+ CAST(@CycleLogHour AS nvarchar(2)) + N':00' + @q + N',' + @crlf +
N' @freq_type = 8,' + @crlf +
N' @freq_interval = 2,' + @crlf + -- 2 = Monday
N' @freq_recurrence_factor = 1,' + @crlf +
N' @active_start_time = ' + CAST(@cycleSchedTS AS nvarchar(10)) + N';' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_attach_schedule' + @crlf +
N' @job_name = N' + @q + N'DBA - Cycle Error Log' + @q + N',' + @crlf +
N' @schedule_name = N' + @q + N'DBA - Cycle Error Log Weekly Mon '
+ CAST(@CycleLogHour AS nvarchar(2)) + N':00' + @q + N';' + @crlf +
@crlf +
N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + N'DBA - Cycle Error Log' + @q + N';' + @crlf +
N'GO' + @crlf;
SELECT @ddl AS ddl;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));
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.
Why EXEC Parameters Cannot Be Expressions
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:
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.
Microsoft’s reference covers sp_add_job, DBCC CHECKDB and sp_delete_job in full.
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
- Troubleshoot RESOURCE_SEMAPHORE Waits (Memory Grant Pressure)
- DBA Scripts: The Complete Guide, the map across every script on this site
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. DBCC CHECKDB is memory-hungry by nature, which is why this job needs real breathing room in your schedule rather than just an off-peak timestamp. Regenerate, review, and run, then confirm ongoing success with Get Maintenance Job Status.
Leave a Reply