DBA Scripts: Generate Index Maintenance Jobs

Q: 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.

July 15, 2026

Peter Whyte

–

DBA Scripts, Maintenance

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_job calls in your own SSMS window before anything touches msdb
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 ... REBUILD directly, not stepping into unfamiliar code
Idempotent generation: the script starts with IF EXISTS ... sp_delete_job before 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';
-- ─────────────────────────────────────────────────────────────────────────────

DECLARE @q              nchar(1)      = NCHAR(39);
DECLARE @crlf           nvarchar(2)   = CHAR(13) + CHAR(10);
DECLARE @ddl            nvarchar(max) = N'';
DECLARE @maintSchedTS   int           = @MaintScheduleHour * 10000;
DECLARE @statsSchedTS   int           = @StatsScheduleHour * 10000;

-- ── Step command: index rebuild / reorganize ──────────────────────────────────
-- Phase 1: collect all fragmented indexes across user databases into a temp table.
--   Uses USE [db] inside dynamic SQL so sys.dm_db_index_physical_stats and catalog
--   views run in the correct database context.
-- Phase 2: apply REBUILD or REORGANIZE for each collected index.
-- @CanOnline is detected from EngineEdition at job runtime, not at generation time.
DECLARE @idxCmd nvarchar(max) = REPLACE(
N'SET NOCOUNT ON;
DECLARE @MinPageCount   int           = <<MIN_PAGES>>;
DECLARE @ReorgPct       decimal(5,1)  = <<REORG_PCT>>;
DECLARE @RebuildPct     decimal(5,1)  = <<REBUILD_PCT>>;
DECLARE @CanOnline      bit           =
    CASE WHEN CAST(SERVERPROPERTY(N|EngineEdition|) AS int) IN (3, 6, 8) THEN 1 ELSE 0 END;

CREATE TABLE #idx (
    db          sysname      NOT NULL,
    schema_name sysname      NOT NULL,
    table_name  sysname      NOT NULL,
    index_name  sysname      NOT NULL,
    frag_pct    decimal(5,1) NOT NULL,
    pages       bigint       NOT NULL,
    action      varchar(10)  NOT NULL
);

DECLARE @db  sysname, @sql nvarchar(max);
DECLARE db_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 AND source_database_id IS NULL
    ORDER BY name;
OPEN db_c;
FETCH NEXT FROM db_c INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N|USE | + QUOTENAME(@db) + N|;
INSERT INTO #idx (db, schema_name, table_name, index_name, frag_pct, pages, action)
SELECT DB_NAME(), s.name, t.name, i.name,
       ips.avg_fragmentation_in_percent, ips.page_count,
       CASE WHEN ips.avg_fragmentation_in_percent >= | + CAST(@RebuildPct AS nvarchar(10)) + N|
            THEN ||REBUILD|| ELSE ||REORGANIZE|| END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N||LIMITED||) ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
JOIN sys.tables  t ON i.object_id   = t.object_id
JOIN sys.schemas s ON t.schema_id   = s.schema_id
WHERE ips.page_count >= | + CAST(@MinPageCount AS nvarchar(10)) + N|
  AND ips.avg_fragmentation_in_percent >= | + CAST(@ReorgPct AS nvarchar(10)) + N|
  AND i.name IS NOT NULL AND i.is_disabled = 0 AND t.is_ms_shipped = 0;|;
    EXEC sp_executesql @sql;
    FETCH NEXT FROM db_c INTO @db;
END
CLOSE db_c;
DEALLOCATE db_c;

DECLARE @schema sysname, @table sysname, @index sysname, @action varchar(10);
DECLARE idx_c CURSOR LOCAL FAST_FORWARD FOR
    SELECT db, schema_name, table_name, index_name, action
    FROM #idx ORDER BY db, frag_pct DESC;
OPEN idx_c;
FETCH NEXT FROM idx_c INTO @db, @schema, @table, @index, @action;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @action = |REBUILD|
        SET @sql = N|USE [| + @db + N|]; ALTER INDEX | + QUOTENAME(@index)
            + N| ON | + QUOTENAME(@schema) + N|.| + QUOTENAME(@table)
            + CASE WHEN @CanOnline = 1
                   THEN N| REBUILD WITH (ONLINE = ON);|
                   ELSE N| REBUILD;|
              END;
    ELSE
        SET @sql = N|USE [| + @db + N|]; ALTER INDEX | + QUOTENAME(@index)
            + N| ON | + QUOTENAME(@schema) + N|.| + QUOTENAME(@table) + N| REORGANIZE;|;
    EXEC sp_executesql @sql;
    FETCH NEXT FROM idx_c INTO @db, @schema, @table, @index, @action;
END
CLOSE idx_c;
DEALLOCATE idx_c;

DROP TABLE #idx;'
, N'|', NCHAR(39));

-- Substitute threshold values (determined at generation time)
SET @idxCmd = REPLACE(@idxCmd, N'<<MIN_PAGES>>', CAST(@MinPageCount AS nvarchar(10)));
SET @idxCmd = REPLACE(@idxCmd, N'<<REORG_PCT>>',  CAST(@FragReorgThreshold AS nvarchar(10)));
SET @idxCmd = REPLACE(@idxCmd, N'<<REBUILD_PCT>>', CAST(@FragRebuildThreshold AS nvarchar(10)));

-- ── Step command: statistics update ──────────────────────────────────────────
DECLARE @statsCmd 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|USE [| + @db + N|]; EXEC sp_updatestats;|;
    EXEC sp_executesql @sql;
    FETCH NEXT FROM c INTO @db;
END
CLOSE c;
DEALLOCATE c;'
, N'|', NCHAR(39));

-- ═══════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════
SET @ddl =
    N'-- =================================================================' + @crlf +
    N'-- Generated by Generate-IndexMaintenanceJobs.sql' + @crlf +
    N'-- Server         : ' + @@SERVERNAME + @crlf +
    N'-- Reorg threshold: ' + CAST(@FragReorgThreshold AS nvarchar(10)) + N'%' + @crlf +
    N'-- Rebuild threshold: ' + CAST(@FragRebuildThreshold AS nvarchar(10)) + N'%' + @crlf +
    N'-- Min page count : ' + CAST(@MinPageCount AS nvarchar(10)) + @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 - Index Maintenance ───────────────────────────────────────────
SET @ddl +=
    @crlf +
    N'-- ==================================================================' + @crlf +
    N'-- Job: DBA - Index Maintenance' + @crlf +
    N'-- Schedule: weekly, Sunday at ' + CAST(@MaintScheduleHour AS nvarchar(2)) + N':00' + @crlf +
    N'-- ==================================================================' + @crlf +
    N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + N'DBA - Index Maintenance' + @q + N')' + @crlf +
    N'    EXEC msdb.dbo.sp_delete_job' + @crlf +
    N'        @job_name              = N' + @q + N'DBA - Index Maintenance' + @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 - Index Maintenance' + @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 - Index Maintenance' + @q + N',' + @crlf +
    N'    @step_id           = 1,' + @crlf +
    N'    @step_name         = N' + @q + N'Rebuild and reorganize fragmented indexes' + @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(@idxCmd, @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 - Index Maintenance Weekly Sun '
        + CAST(@MaintScheduleHour 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(@maintSchedTS AS nvarchar(10)) + N';' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_attach_schedule' + @crlf +
    N'    @job_name      = N' + @q + N'DBA - Index Maintenance' + @q + N',' + @crlf +
    N'    @schedule_name = N' + @q + N'DBA - Index Maintenance Weekly Sun '
        + CAST(@MaintScheduleHour AS nvarchar(2)) + N':00' + @q + N';' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + N'DBA - Index Maintenance' + @q + N';' + @crlf +
    N'GO' + @crlf;

-- ── Job 2: DBA - Statistics Update ───────────────────────────────────────────
SET @ddl +=
    @crlf +
    N'-- ==================================================================' + @crlf +
    N'-- Job: DBA - Statistics Update' + @crlf +
    N'-- Schedule: weekly, Saturday at ' + CAST(@StatsScheduleHour AS nvarchar(2)) + N':00' + @crlf +
    N'-- Note: sp_updatestats only updates stats where rows have changed.' + @crlf +
    N'-- ==================================================================' + @crlf +
    N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + N'DBA - Statistics Update' + @q + N')' + @crlf +
    N'    EXEC msdb.dbo.sp_delete_job' + @crlf +
    N'        @job_name              = N' + @q + N'DBA - Statistics Update' + @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 - Statistics Update' + @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 - Statistics Update' + @q + N',' + @crlf +
    N'    @step_id           = 1,' + @crlf +
    N'    @step_name         = N' + @q + N'Update statistics on 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(@statsCmd, @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 - Statistics Update Weekly Sat '
        + CAST(@StatsScheduleHour 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(@statsSchedTS AS nvarchar(10)) + N';' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_attach_schedule' + @crlf +
    N'    @job_name      = N' + @q + N'DBA - Statistics Update' + @q + N',' + @crlf +
    N'    @schedule_name = N' + @q + N'DBA - Statistics Update Weekly Sat '
        + CAST(@StatsScheduleHour AS nvarchar(2)) + N':00' + @q + N';' + @crlf +
    @crlf +
    N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + N'DBA - Statistics Update' + @q + N';' + @crlf +
    N'GO' + @crlf;

SELECT @ddl AS ddl;

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:

sql/maintenance/Generate-IndexMaintenanceJobs.sql

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 = 1 in sysjobhistory means 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_updatestats only 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_desc for the affected database before assuming damage

Best Practices

Review the generated DDL before running it. The script only produces text; nothing touches msdb until 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_job pattern means the old job is cleanly replaced, not duplicated.
Don’t schedule DBA - Index Maintenance to 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, or 3908, check sys.databases.state_desc for 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.

Microsoft Learn: sp_add_job · sys.databases · sp_updatestats

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
DBA Scripts: The Complete Guide, the map across every script on this site

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. This job doesn’t have a script bug, but it does carry a real memory-pressure risk: 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.