Part of the DBA-Tools Project.
A Readable Generator for Index Rebuilds and Statistics Updates
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 an index maintenance 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 two jobs: 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). It only produces text, nothing touches msdb until you review the output and run it yourself.
Why a Generated, Readable Index Maintenance Framework Matters
- A framework you can’t read is one you can’t fully trust, and most DBAs run someone else’s index maintenance without ever fully reading what it does
- 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
ALTER INDEX ... REBUILDdirectly, 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 index maintenance on a new instance that has none
- Replacing an ad-hoc set of index maintenance jobs with a documented, regenerable one
- Fragmentation or stale statistics showing up repeatedly in query performance investigations
- Any time a threshold (fragmentation %, minimum page count, schedule) needs to change, edit the parameter block and regenerate rather than hand-editing job steps in SSMS
The Script
/*
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-index-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';
-- ─────────────────────────────────────────────────────────────────────────────
(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-IndexMaintenanceJobs
# To generate against a remote server:
.\run.ps1 Generate-IndexMaintenanceJobs -ServerInstance SQLSERVER01
This script lives in the repo at:
Not a Script Bug — Memory Pressure Can Make Index Maintenance Fail in Ways That Look Worse Than They Are
A real, reproducible finding from running DBA - Index Maintenance against a resource-constrained local SQL Server 2025 instance, worth building into how this job gets scheduled. Not a bug in the generator script itself.
DBA - Index Maintenance failed outright under memory 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 a RESOURCE_SEMAPHORE wait repeatedly under the same pressure. 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: the job isn’t broken, but it assumes it’ll get the memory it asks for. On a shared or resource-constrained box, that assumption fails loudly, 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 - Index Maintenance, check what else is actually competing for memory on that host, not just whether the clock says off-peak. 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 against a local SQL Server 2025 instance:
| Job | run_status | Duration | Message |
|---|---|---|---|
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 finding above |
The failed run is shown honestly rather than replaced with a cleaner example, it’s the more instructive result: what memory pressure actually looks like in job history, and why it isn’t corruption.
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 updated count on Statistics Update is normal on a lab-sized instance.
sp_updatestatsonly touches tables with modified rows since the last update - An index maintenance job that fails with a scary error cascade under load is a resource-scheduling problem, not a script bug. Check
sys.databases.state_descfor the affected database before assuming damage
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. - Don’t schedule
DBA - Index Maintenanceto overlap with backups or integrity checks on an instance under memory pressure, an index rebuild losing its memory grant mid-transaction is a worse position to be in than a job that simply queues. - 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:
- SQL Server Maintenance Job Framework (hub)
- Generate Backup Jobs
- Generate Database Integrity and Housekeeping Jobs
- Get Maintenance Job Status
- Get Index Fragmentation
Frequently Asked Questions
My index maintenance job failed with errors 802, 9001, and 3908, is my database corrupted?
Check sys.databases.state_desc for that database first. If it shows ONLINE with no unusual flags, SQL Server’s own crash recovery already handled a resource-starved rollback, this is what memory pressure looks like in job history, not necessarily corruption. Run DBCC CHECKDB to confirm once memory pressure clears.
Why does the job detect edition instead of always using ONLINE = ON?
Online index rebuilds require Enterprise or Developer edition. The script checks SERVERPROPERTY('EngineEdition') at job runtime and falls back to an offline rebuild on Standard or Web edition automatically, rather than failing the job outright on unsupported editions.
Summary
DBA - Index Maintenance and DBA - Statistics Update come out of one generator script you can read end to end before running. No script bug turned up testing this against a live instance, but a real memory-pressure finding did: an index rebuild that loses its memory grant mid-transaction throws an error cascade that reads far worse than what’s actually wrong. That’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