sql server hub / maintenance plan guide

SQL Servermaintenance plan guide

A SQL Server maintenance plan is the scheduled DBA work that keeps backups, integrity checks, statistics, cleanup, alerts, and job history aligned with the current workload.

Use this guide to review SQL Agent jobs, backup history, CHECKDB coverage, index and statistics jobs, cleanup retention, alert routing, and maintenance-window overlap. For the wider environment review, use a SQL Server health audit.

jobsbackupsCHECKDBcleanup

Guide

Operational guide~16 min readUpdated 23 May 2026

LinkedIn X Email

01What a SQL Server maintenance plan is
02When to review a SQL Server maintenance plan
03What SQL Server maintenance jobs should cover
04How to find SQL Server maintenance jobs
05How to read SQL Server Agent maintenance history
06How backup jobs belong in a SQL Server maintenance plan
07How DBCC CHECKDB belongs in a SQL Server maintenance plan
08How index and statistics maintenance should be decided
09How cleanup and retention should work
10How to check maintenance windows and job overlap
11How SQL Server maintenance failures should be reported
12What not to change in maintenance jobs
13When to request a SQL Server maintenance review

What a SQL Server maintenance plan is

A SQL Server maintenance plan is the routine DBA work that keeps recovery, integrity checks, statistics, cleanup, alerting, and job history useful as the database changes.

It is not only the SSMS Maintenance Plan Wizard. A real maintenance plan can be SQL Agent jobs, Ola Hallengren scripts, custom stored procedures, vendor jobs, backup jobs, CHECKDB jobs, cleanup jobs, and alerting around those jobs.

When to review a SQL Server maintenance plan

Review the plan when a SQL Server changes owner, when jobs are old or partly disabled, before migrations and upgrades, after backup or CHECKDB uncertainty, after repeated job failures, or when maintenance begins to collide with production work.

A DBA or IT department is taking responsibility for an older SQL Server.

Maintenance jobs exist, but nobody can explain what they do or why they run when they do.

Backups complete, but restore testing or restore history is weak.

CHECKDB, index work, or cleanup has not been reviewed after database growth.

Jobs fail quietly, retry often, or run past the maintenance window.

What SQL Server maintenance jobs should cover

A practical review starts with what actually runs. Find what the jobs touch, what they skip, how long they take, who gets notified, and whether the schedule still makes sense for the current workload.

Maintenance area	SQL output to check	Decision it supports
Backups	Recent full, differential, and log backup history plus file locations.	Whether recovery targets and restore testing need follow-up.
Integrity checks	DBCC CHECKDB jobs, recent runs, failures, and chosen options.	Whether corruption checks are actually happening.
Index and statistics work	Commands, runtime, log impact, blocking, and statistics updates.	Whether the work matches the workload or only follows an old habit.
Cleanup and retention	Backup-file cleanup, msdb cleanup, job history, logs, and retention values.	Whether cleanup protects storage without deleting useful history.
Alerts and owners	SQL Agent owners, operators, notification levels, and Database Mail.	Whether failures reach someone who can act.
Maintenance windows	Schedules, duration history, overlap with ETL, reporting, and HA roles.	Whether maintenance still fits the current business load.

How to find SQL Server maintenance jobs

Start with instance context, then list SQL Agent jobs that look like maintenance. Pay attention to disabled jobs, disabled schedules, unclear owners, old modified dates, duplicate names, and jobs that are not in the maintenance category but still run maintenance work.

How to check SQL Server maintenance context

Reads SQL Server version, uptime, database count, HADR flag, and SQL Agent service status when available.

SELECT
    SERVERPROPERTY('ServerName') AS server_name,
    SERVERPROPERTY('MachineName') AS machine_name,
    SERVERPROPERTY('Edition') AS edition,
    SERVERPROPERTY('ProductVersion') AS product_version,
    SERVERPROPERTY('ProductLevel') AS product_level,
    SERVERPROPERTY('ProductUpdateLevel') AS product_update_level,
    SERVERPROPERTY('IsHadrEnabled') AS is_hadr_enabled,
    osi.sqlserver_start_time,
    COUNT(DISTINCT d.database_id) AS database_count,
    MAX(CASE
        WHEN s.servicename LIKE 'SQL Server Agent%' THEN s.status_desc
    END) AS sql_agent_status
FROM sys.dm_os_sys_info AS osi
CROSS JOIN sys.databases AS d
LEFT JOIN sys.dm_server_services AS s
    ON s.servicename LIKE 'SQL Server Agent%'
GROUP BY
    osi.sqlserver_start_time;

Uptime matters because job history and DMV counters are easier to interpret when the restart date is known. Edition, version, and HADR state can change what maintenance jobs should do. sys.dm_server_services may require elevated access and may not be available in every hosted environment. If service state is hidden, continue with SQL Agent job history and the surrounding monitoring data.

How to find maintenance-related SQL Agent jobs

Lists likely maintenance jobs, schedules, owners, enabled state, and next run metadata.

WITH Jobs AS (
    SELECT
        j.job_id,
        j.name,
        j.enabled,
        j.owner_sid,
        j.category_id,
        j.date_created,
        j.date_modified,
        UPPER(j.name) AS normalized_job_name
    FROM msdb.dbo.sysjobs AS j
)
SELECT
    j.name AS job_name,
    CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    SUSER_SNAME(j.owner_sid) AS job_owner,
    c.name AS category_name,
    j.date_created,
    j.date_modified,
    s.name AS schedule_name,
    CASE WHEN s.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS schedule_status,
    s.freq_type,
    s.freq_interval,
    CASE
        WHEN s.active_start_time IS NULL THEN NULL
        ELSE STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(6), s.active_start_time), 6), 3, 0, ':'), 6, 0, ':')
    END AS active_start_time,
    CASE
        WHEN s.active_end_time IS NULL THEN NULL
        ELSE STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(6), s.active_end_time), 6), 3, 0, ':'), 6, 0, ':')
    END AS active_end_time,
    CASE
        WHEN js.next_run_date > 0 THEN msdb.dbo.agent_datetime(js.next_run_date, js.next_run_time)
    END AS next_run_datetime
FROM Jobs AS j
LEFT JOIN msdb.dbo.syscategories AS c
    ON c.category_id = j.category_id
LEFT JOIN msdb.dbo.sysjobschedules AS js
    ON js.job_id = j.job_id
LEFT JOIN msdb.dbo.sysschedules AS s
    ON s.schedule_id = js.schedule_id
WHERE j.normalized_job_name LIKE '%MAINTENANCE%'
   OR j.normalized_job_name LIKE '%BACKUP%'
   OR j.normalized_job_name LIKE '%CHECKDB%'
   OR j.normalized_job_name LIKE '%INTEGRITY%'
   OR j.normalized_job_name LIKE '%INDEX%'
   OR j.normalized_job_name LIKE '%STATISTICS%'
   OR j.normalized_job_name LIKE '%CLEANUP%'
   OR UPPER(c.name) LIKE '%DATABASE MAINTENANCE%'
ORDER BY j.enabled DESC, j.name, s.name;

This finds obvious maintenance jobs by job name, category, and schedule metadata. Disabled jobs and disabled schedules are not automatically bad, but they should be intentional. Unexpected owners can break jobs after login changes or staff changes. SQL Agent next-run metadata can lag because sysjobschedules refreshes on an interval. The schedule columns need interpretation; use them to find what deserves a closer look.

How to inspect maintenance job commands

Searches job steps for backup, CHECKDB, index, statistics, and cleanup commands.

WITH JobSteps AS (
    SELECT
        j.name AS job_name,
        j.enabled,
        s.step_id,
        s.step_name,
        s.subsystem,
        s.command,
        UPPER(j.name) AS normalized_job_name,
        UPPER(s.command) AS normalized_command
    FROM msdb.dbo.sysjobs AS j
    JOIN msdb.dbo.sysjobsteps AS s
        ON s.job_id = j.job_id
)
SELECT
    job_name,
    CASE WHEN enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    step_id,
    step_name,
    subsystem,
    CASE
        WHEN normalized_command LIKE '%DBCC CHECKDB%' OR normalized_command LIKE '%DATABASEINTEGRITYCHECK%' THEN 'CHECKDB'
        WHEN normalized_command LIKE '%BACKUP DATABASE%' THEN 'Database backup'
        WHEN normalized_command LIKE '%BACKUP LOG%' THEN 'Log backup'
        WHEN normalized_command LIKE '%ALTER INDEX%' OR normalized_command LIKE '%INDEXOPTIMIZE%' THEN 'Index maintenance'
        WHEN normalized_command LIKE '%UPDATE STATISTICS%' OR normalized_command LIKE '%SP_UPDATESTATS%' THEN 'Statistics update'
        WHEN normalized_command LIKE '%SP_DELETE_BACKUPHISTORY%' THEN 'Backup history cleanup'
        WHEN normalized_command LIKE '%SP_PURGE_JOBHISTORY%' THEN 'Job history cleanup'
        WHEN normalized_command LIKE '%XP_DELETE_FILE%' THEN 'File cleanup'
        ELSE 'Other possible maintenance'
    END AS maintenance_type,
    LEFT(REPLACE(REPLACE(command, CHAR(13), ' '), CHAR(10), ' '), 600) AS command_preview
FROM JobSteps
WHERE normalized_command LIKE '%DBCC CHECKDB%'
   OR normalized_command LIKE '%DATABASEINTEGRITYCHECK%'
   OR normalized_command LIKE '%BACKUP DATABASE%'
   OR normalized_command LIKE '%BACKUP LOG%'
   OR normalized_command LIKE '%ALTER INDEX%'
   OR normalized_command LIKE '%INDEXOPTIMIZE%'
   OR normalized_command LIKE '%UPDATE STATISTICS%'
   OR normalized_command LIKE '%SP_UPDATESTATS%'
   OR normalized_command LIKE '%SP_DELETE_BACKUPHISTORY%'
   OR normalized_command LIKE '%SP_PURGE_JOBHISTORY%'
   OR normalized_command LIKE '%XP_DELETE_FILE%'
ORDER BY job_name, step_id;

This catches maintenance work even when the job name is vague. Command previews help identify what the job actually does without editing anything. A job can call PowerShell, CmdExec, or a stored procedure that hides deeper logic. Use this as a map, not as confirmation that each task is correct.

How to read SQL Server Agent maintenance history

Job history shows what SQL Server Agent recorded, not everything that matters. Still, it is usually the fastest way to find failed steps, retries, canceled work, and jobs that are quietly getting too long for the window.

How to find failed or retried maintenance jobs

Finds recent job failures, retries, canceled steps, and long maintenance-related job steps.

WITH JobHistory AS (
    SELECT
        j.name AS job_name,
        UPPER(j.name) AS normalized_job_name,
        h.step_id,
        h.step_name,
        h.run_status,
        msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_start_time,
        ((h.run_duration / 10000) * 3600)
          + (((h.run_duration % 10000) / 100) * 60)
          + (h.run_duration % 100) AS duration_seconds,
        h.message
    FROM msdb.dbo.sysjobhistory AS h
    JOIN msdb.dbo.sysjobs AS j
        ON j.job_id = h.job_id
    WHERE h.run_date > 0
      AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -30, GETDATE()), 112))
)
SELECT TOP (150)
    job_name,
    step_id,
    step_name,
    CASE run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'In progress'
        ELSE CONVERT(varchar(20), run_status)
    END AS run_status,
    run_start_time,
    duration_seconds,
    message
FROM JobHistory
WHERE (
      run_status <> 1
      OR duration_seconds >= 1800
      OR normalized_job_name LIKE '%MAINTENANCE%'
      OR normalized_job_name LIKE '%BACKUP%'
      OR normalized_job_name LIKE '%CHECKDB%'
      OR normalized_job_name LIKE '%INTEGRITY%'
      OR normalized_job_name LIKE '%INDEX%'
      OR normalized_job_name LIKE '%STATISTICS%'
      OR normalized_job_name LIKE '%CLEANUP%'
  )
ORDER BY run_start_time DESC, duration_seconds DESC;

A final success can hide failed or retried steps earlier in the job. Long duration is context-dependent; this query uses 30 minutes as a starting point and handles runs over 24 hours. Job history retention may hide older failures. Review the message column before deciding whether the failure matters.

How to check maintenance job duration drift

Summarizes successful maintenance job duration over the last 90 days.

WITH JobRuns AS (
    SELECT
        j.name AS job_name,
        UPPER(j.name) AS normalized_job_name,
        msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_start_time,
        ((h.run_duration / 10000) * 3600)
          + (((h.run_duration % 10000) / 100) * 60)
          + (h.run_duration % 100) AS duration_seconds,
        ROW_NUMBER() OVER (
            PARTITION BY j.job_id
            ORDER BY h.run_date DESC, h.run_time DESC
        ) AS run_number
    FROM msdb.dbo.sysjobhistory AS h
    JOIN msdb.dbo.sysjobs AS j
        ON j.job_id = h.job_id
    WHERE h.step_id = 0
      AND h.run_status = 1
      AND h.run_date > 0
      AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -90, GETDATE()), 112))
      AND (
          UPPER(j.name) LIKE '%MAINTENANCE%'
          OR UPPER(j.name) LIKE '%BACKUP%'
          OR UPPER(j.name) LIKE '%CHECKDB%'
          OR UPPER(j.name) LIKE '%INTEGRITY%'
          OR UPPER(j.name) LIKE '%INDEX%'
          OR UPPER(j.name) LIKE '%STATISTICS%'
          OR UPPER(j.name) LIKE '%CLEANUP%'
      )
)
SELECT
    job_name,
    COUNT(*) AS successful_runs,
    MIN(duration_seconds) AS min_duration_seconds,
    MAX(duration_seconds) AS max_duration_seconds,
    AVG(duration_seconds) AS avg_duration_seconds,
    MAX(CASE WHEN run_number = 1 THEN duration_seconds END) AS latest_duration_seconds
FROM JobRuns
GROUP BY job_name
ORDER BY max_duration_seconds DESC;

Duration growth often points to data growth, storage pressure, or a schedule that no longer fits. Compare latest duration against average and maximum duration. This does not show failed runs; use it alongside the failure query. Large duration spread deserves a look before the job starts colliding with business hours.

Outcome	What it means	Review note
Success	The step reported success.	Still check what the step did and which databases it covered.
Failure	The step failed.	Review the message, affected database, retries, and downstream work skipped.
Retry	SQL Agent retried the step.	A retry can hide a recurring problem if the final outcome is green.
Canceled	The job or step was stopped.	Usually means the maintenance window, blocking, or operator action needs review.
Long-running	Duration has grown or regularly exceeds the window.	Data growth or bad job design may now collide with production work.
No recent run	The job exists but has no recent history.	Disabled schedules, deleted history, or broken ownership may be hiding the real state.

How backup jobs belong in a SQL Server maintenance plan

Backup jobs are the part of maintenance most people feel best about, sometimes too quickly. Check the latest backup by type, the age of each backup, the duration, the size, and the recovery model. Then check whether anyone has restored them.

For deeper backup and restore review, use the SQL Server backup guide and SQL Server recovery guide.

How to review recent backup history

Shows the latest full, differential, and log backups found in msdb for each database.

WITH LastBackups AS (
    SELECT
        bs.backup_set_id,
        bs.media_set_id,
        bs.database_name,
        bs.type,
        bs.backup_start_date,
        bs.backup_finish_date,
        bs.backup_size,
        bs.compressed_backup_size,
        bs.is_copy_only,
        bs.has_backup_checksums,
        ROW_NUMBER() OVER (
            PARTITION BY bs.database_name, bs.type
            ORDER BY bs.backup_finish_date DESC
        ) AS rn
    FROM msdb.dbo.backupset AS bs
    WHERE bs.backup_finish_date >= DATEADD(day, -30, GETDATE())
)
SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    CASE lb.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
        ELSE lb.type
    END AS backup_type,
    lb.backup_start_date,
    lb.backup_finish_date,
    DATEDIFF(hour, lb.backup_finish_date, GETDATE()) AS hours_since_backup,
    DATEDIFF(minute, lb.backup_start_date, lb.backup_finish_date) AS duration_minutes,
    CONVERT(decimal(18,2), lb.backup_size / 1048576.0) AS backup_size_mb,
    CONVERT(decimal(18,2), lb.compressed_backup_size / 1048576.0) AS compressed_size_mb,
    lb.is_copy_only,
    lb.has_backup_checksums,
    media.physical_device_names
FROM sys.databases AS d
LEFT JOIN LastBackups AS lb
    ON lb.database_name = d.name
   AND lb.rn = 1
OUTER APPLY (
    SELECT STUFF((
        SELECT N'; ' + bmf.physical_device_name
        FROM msdb.dbo.backupmediafamily AS bmf
        WHERE bmf.media_set_id = lb.media_set_id
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)'), 1, 2, N'') AS physical_device_names
) AS media
WHERE d.name NOT IN (N'tempdb')
ORDER BY d.name, backup_type;

This reads msdb history; it does not confirm files are present or restore-tested. physical_device_names shows where SQL Server recorded the backup destination, not whether the file is still accessible. Copy-only and checksum flags help separate ad hoc backups from normal maintenance behavior. Full recovery model databases normally need log backups if point-in-time recovery matters. Long backup durations can affect the maintenance window and downstream jobs.

How to find backup maintenance gaps

Flags missing full backups, missing log backups for full/bulk-logged databases, and older full backups.

SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full_backup,
    MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_diff_backup,
    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup,
    CASE
        WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) IS NULL
            THEN 'No full backup found'
        WHEN d.recovery_model_desc IN ('FULL', 'BULK_LOGGED')
             AND MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) IS NULL
            THEN 'Full/Bulk-logged with no log backup found'
        WHEN MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) < DATEADD(day, -7, GETDATE())
            THEN 'Full backup older than 7 days'
        ELSE 'Review schedule against RPO'
    END AS review_note
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS bs
    ON bs.database_name = d.name
   AND bs.backup_finish_date >= DATEADD(day, -60, GETDATE())
WHERE d.name NOT IN (N'tempdb')
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;

The thresholds are starting points, not universal rules. Compare backup age to RPO, not to a generic schedule. A missing log backup may mean the recovery model is wrong or the log chain is broken. Do not clean up old files until the retention and restore path are clear.

How DBCC CHECKDB belongs in a SQL Server maintenance plan

Integrity checks answer a different question than backups. Backups tell you files were produced. CHECKDB helps find corruption before restore work or production incidents make the problem urgent.

The right schedule depends on size, workload, maintenance windows, storage, and whether checks can run against a restored copy. Large databases may use PHYSICAL_ONLY between fuller checks, but repair options are not normal maintenance.

How to find CHECKDB and integrity jobs

Finds SQL Agent job steps that appear to run DBCC CHECKDB or integrity checks, plus recent history.

WITH CheckdbSteps AS (
    SELECT
        j.job_id,
        j.name AS job_name,
        j.enabled,
        s.step_id,
        s.step_name,
        s.command,
        UPPER(j.name) AS normalized_job_name,
        UPPER(s.command) AS normalized_command
    FROM msdb.dbo.sysjobs AS j
    JOIN msdb.dbo.sysjobsteps AS s
        ON s.job_id = j.job_id
    WHERE UPPER(s.command) LIKE '%DBCC CHECKDB%'
       OR UPPER(s.command) LIKE '%DATABASEINTEGRITYCHECK%'
       OR UPPER(j.name) LIKE '%CHECKDB%'
       OR UPPER(j.name) LIKE '%INTEGRITY%'
),
StepHistory AS (
    SELECT
        h.job_id,
        h.step_id,
        MAX(msdb.dbo.agent_datetime(h.run_date, h.run_time)) AS last_step_run,
        SUM(CASE WHEN h.run_status = 0 THEN 1 ELSE 0 END) AS step_failures_in_history
    FROM msdb.dbo.sysjobhistory AS h
    WHERE h.run_date > 0
      AND h.step_id > 0
      AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -90, GETDATE()), 112))
    GROUP BY h.job_id, h.step_id
)
SELECT
    cs.job_name,
    CASE WHEN cs.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    cs.step_id,
    cs.step_name,
    CASE
        WHEN cs.normalized_command LIKE '%PHYSICAL_ONLY%' THEN 'CHECKDB with PHYSICAL_ONLY'
        WHEN cs.normalized_command LIKE '%REPAIR_%' THEN 'CHECKDB with repair option'
        WHEN cs.normalized_command LIKE '%DATABASEINTEGRITYCHECK%' THEN 'Ola Hallengren DatabaseIntegrityCheck'
        ELSE 'CHECKDB or integrity check'
    END AS integrity_work_type,
    sh.last_step_run,
    ISNULL(sh.step_failures_in_history, 0) AS step_failures_in_history,
    LEFT(REPLACE(REPLACE(cs.command, CHAR(13), ' '), CHAR(10), ' '), 800) AS command_preview
FROM CheckdbSteps AS cs
LEFT JOIN StepHistory AS sh
    ON sh.job_id = cs.job_id
   AND sh.step_id = cs.step_id
ORDER BY cs.job_name, cs.step_id;

This finds job steps that contain DBCC CHECKDB or obvious integrity naming. PHYSICAL_ONLY can be useful for frequent large-database checks, but it is not the same as a full CHECKDB strategy. Repair options should not be treated as routine maintenance; restore is normally the safer starting point. A custom stored procedure can hide the actual command; inspect command_preview carefully. No rows returned may mean no SQL Agent CHECKDB job exists, or the logic is hidden elsewhere.

Environment	Likely approach	Warning
Small databases	Full CHECKDB is often practical on a regular schedule.	Still confirm it runs and alerts properly.
Large databases	The schedule may need off-peak windows, restored-copy checks, or phased review.	Do not add heavy CHECKDB work blindly during business load.
Tight window	Prioritize coverage, history, and failure alerting first.	A perfect plan that never fits the window is not useful.
No CHECKDB history	Treat as an integrity-coverage gap.	Decide a safe first run and watch resource impact.

How index and statistics maintenance should be decided

Index maintenance is often the loudest part of the plan and not always the most useful. Rebuilding every index on a schedule can create I/O, log growth, blocking, and long windows without fixing the workload problem.

Fragmentation is only one input. Statistics, runtime, log growth, blocking, and the actual workload matter more than a weekly rebuild habit. Review index and statistics jobs with the SQL Server indexing guide nearby. The point is not to remove index work. The point is to make it match the workload.

How to find index and statistics maintenance jobs

Finds job steps that appear to rebuild/reorganize indexes or update statistics.

WITH JobSteps AS (
    SELECT
        j.name AS job_name,
        j.enabled,
        s.step_id,
        s.step_name,
        s.command,
        UPPER(j.name) AS normalized_job_name,
        UPPER(s.command) AS normalized_command
    FROM msdb.dbo.sysjobs AS j
    JOIN msdb.dbo.sysjobsteps AS s
        ON s.job_id = j.job_id
)
SELECT
    job_name,
    CASE WHEN enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    step_id,
    step_name,
    CASE
        WHEN normalized_command LIKE '%INDEXOPTIMIZE%' THEN 'Ola Hallengren IndexOptimize'
        WHEN normalized_command LIKE '%ALTER INDEX%REBUILD%' THEN 'Index rebuild'
        WHEN normalized_command LIKE '%ALTER INDEX%REORGANIZE%' THEN 'Index reorganize'
        WHEN normalized_command LIKE '%UPDATE STATISTICS%' THEN 'Statistics update'
        WHEN normalized_command LIKE '%SP_UPDATESTATS%' THEN 'sp_updatestats'
        ELSE 'Possible index/statistics work'
    END AS maintenance_type,
    LEFT(REPLACE(REPLACE(command, CHAR(13), ' '), CHAR(10), ' '), 800) AS command_preview
FROM JobSteps
WHERE normalized_command LIKE '%ALTER INDEX%'
   OR normalized_command LIKE '%INDEXOPTIMIZE%'
   OR normalized_command LIKE '%UPDATE STATISTICS%'
   OR normalized_command LIKE '%SP_UPDATESTATS%'
   OR normalized_job_name LIKE '%INDEX%'
   OR normalized_job_name LIKE '%STATISTICS%'
ORDER BY job_name, step_id;

Look for broad commands that rebuild or update everything without filters. Ola Hallengren IndexOptimize jobs still need threshold and runtime review. Statistics work can matter more than fragmentation in many systems. Index maintenance should be checked against runtime, log growth, and blocking impact. This query finds jobs; it does not judge whether the chosen thresholds are good.

Prefer targeted work over global rebuild rituals.

Check whether statistics updates are part of the plan.

Watch index job duration, log growth, blocking, and I/O pressure.

Review after data growth, schema changes, and performance incidents.

How cleanup and retention should work

Cleanup is useful when it protects storage and keeps history manageable. It is dangerous when it deletes backup files, job history, restore history, or logs before retention and troubleshooting needs are known.

How to find cleanup and retention jobs

Finds job steps that appear to delete backup files, backup history, SQL Agent history, or maintenance logs.

WITH JobSteps AS (
    SELECT
        j.name AS job_name,
        j.enabled,
        s.step_id,
        s.step_name,
        s.command,
        UPPER(j.name) AS normalized_job_name,
        UPPER(s.command) AS normalized_command
    FROM msdb.dbo.sysjobs AS j
    JOIN msdb.dbo.sysjobsteps AS s
        ON s.job_id = j.job_id
)
SELECT
    job_name,
    CASE WHEN enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    step_id,
    step_name,
    CASE
        WHEN normalized_command LIKE '%XP_DELETE_FILE%' THEN 'File cleanup'
        WHEN normalized_command LIKE '%SP_DELETE_BACKUPHISTORY%' THEN 'Backup history cleanup'
        WHEN normalized_command LIKE '%SP_PURGE_JOBHISTORY%' THEN 'Job history cleanup'
        WHEN normalized_command LIKE '%MAINTENANCE CLEANUP%' THEN 'Maintenance cleanup'
        ELSE 'Possible cleanup'
    END AS cleanup_type,
    LEFT(REPLACE(REPLACE(command, CHAR(13), ' '), CHAR(10), ' '), 800) AS command_preview
FROM JobSteps
WHERE normalized_command LIKE '%XP_DELETE_FILE%'
   OR normalized_command LIKE '%SP_DELETE_BACKUPHISTORY%'
   OR normalized_command LIKE '%SP_PURGE_JOBHISTORY%'
   OR normalized_command LIKE '%MAINTENANCE CLEANUP%'
   OR normalized_job_name LIKE '%CLEANUP%'
ORDER BY job_name, step_id;

Review retention values before changing or rerunning cleanup jobs. Backup file cleanup should match restore and retention requirements. Job history cleanup can hide patterns you need during maintenance review. Do not delete old files just because storage is tight; check recovery impact first.

How to check msdb history retention

Shows rough row counts and oldest/newest records for backup and SQL Agent history.

SELECT
    'backupset' AS table_name,
    COUNT(*) AS row_count,
    MIN(backup_finish_date) AS oldest_record,
    MAX(backup_finish_date) AS newest_record
FROM msdb.dbo.backupset
UNION ALL
SELECT
    'sysjobhistory',
    COUNT(*),
    MIN(msdb.dbo.agent_datetime(run_date, run_time)),
    MAX(msdb.dbo.agent_datetime(run_date, run_time))
FROM msdb.dbo.sysjobhistory
WHERE run_date > 0;

Very short history can make maintenance review harder. Very large history may indicate cleanup needs review. History volume is not a problem by itself; context matters. Use this to decide whether retention is too aggressive or too loose.

How to check maintenance windows and job overlap

A maintenance plan can be technically correct and still badly scheduled. Heavy jobs stacked into the same hour can create the very problems the plan is supposed to prevent.

How to check maintenance schedule overlap

Lists active maintenance-like jobs by schedule start time to find likely overlap.

WITH Jobs AS (
    SELECT
        j.job_id,
        j.name,
        j.enabled,
        UPPER(j.name) AS normalized_job_name
    FROM msdb.dbo.sysjobs AS j
)
SELECT
    j.name AS job_name,
    CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    s.name AS schedule_name,
    CASE WHEN s.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS schedule_status,
    s.freq_type,
    s.freq_interval,
    STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(6), s.active_start_time), 6), 3, 0, ':'), 6, 0, ':') AS active_start_time,
    STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(6), s.active_end_time), 6), 3, 0, ':'), 6, 0, ':') AS active_end_time,
    CASE
        WHEN js.next_run_date > 0 THEN msdb.dbo.agent_datetime(js.next_run_date, js.next_run_time)
    END AS next_run_datetime
FROM Jobs AS j
JOIN msdb.dbo.sysjobschedules AS js
    ON js.job_id = j.job_id
JOIN msdb.dbo.sysschedules AS s
    ON s.schedule_id = js.schedule_id
WHERE j.enabled = 1
  AND (
      j.normalized_job_name LIKE '%MAINTENANCE%'
      OR j.normalized_job_name LIKE '%BACKUP%'
      OR j.normalized_job_name LIKE '%CHECKDB%'
      OR j.normalized_job_name LIKE '%INTEGRITY%'
      OR j.normalized_job_name LIKE '%INDEX%'
      OR j.normalized_job_name LIKE '%STATISTICS%'
      OR j.normalized_job_name LIKE '%CLEANUP%'
  )
ORDER BY s.active_start_time, j.name;

SQL Agent schedule metadata is awkward; treat this as a starting point. The next_run_datetime value can lag because SQL Server Agent updates sysjobschedules periodically. Compare schedule start time with real job duration from job history. Look for backup, CHECKDB, index work, cleanup, ETL, and reporting overlap. Availability Group jobs may need replica-role logic, not identical schedules everywhere.

Bad pattern	Risk	Better pattern
Full backup overlaps index rebuild	I/O pressure, longer backups, and longer rebuilds.	Separate heavy I/O work or stagger by database.
CHECKDB runs into business hours	User workload competes with integrity checks.	Move, split, or run against a restored copy where appropriate.
Cleanup runs before verification	Useful files or history disappear too early.	Keep retention tied to recovery and review needs.
AG replicas all run the same work	Duplicate load, conflicting backup preference, or noisy alerts.	Review replica role, backup preference, and job targeting.
Maintenance starts before ETL ends	Blocking, failed steps, or runtime drift.	Schedule around real workload timing, not an old schedule.

How SQL Server maintenance failures should be reported

Maintenance without alerting is mostly hope. Failed jobs should reach someone who can act, and job ownership should not depend on an old login nobody wants to touch.

For broader alerting and monitoring coverage, pair this with the SQL Server monitoring guide and the monitoring gaps page.

How to check job owners and notifications

Shows maintenance-like jobs, owners, notification levels, and configured email operators.

WITH Jobs AS (
    SELECT
        j.job_id,
        j.name,
        j.enabled,
        j.owner_sid,
        j.notify_level_email,
        j.notify_email_operator_id,
        UPPER(j.name) AS normalized_job_name
    FROM msdb.dbo.sysjobs AS j
)
SELECT
    j.name AS job_name,
    CASE WHEN j.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS job_status,
    SUSER_SNAME(j.owner_sid) AS job_owner,
    CASE j.notify_level_email
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'When the job succeeds'
        WHEN 2 THEN 'When the job fails'
        WHEN 3 THEN 'When the job completes'
        ELSE CONVERT(varchar(20), j.notify_level_email)
    END AS email_notification_level,
    o.name AS email_operator,
    CASE WHEN o.enabled = 1 THEN 'Enabled' ELSE 'Disabled or missing' END AS operator_status,
    j.notify_email_operator_id
FROM Jobs AS j
LEFT JOIN msdb.dbo.sysoperators AS o
    ON o.id = j.notify_email_operator_id
WHERE j.normalized_job_name LIKE '%MAINTENANCE%'
   OR j.normalized_job_name LIKE '%BACKUP%'
   OR j.normalized_job_name LIKE '%CHECKDB%'
   OR j.normalized_job_name LIKE '%INTEGRITY%'
   OR j.normalized_job_name LIKE '%INDEX%'
   OR j.normalized_job_name LIKE '%STATISTICS%'
   OR j.normalized_job_name LIKE '%CLEANUP%'
ORDER BY j.notify_level_email, j.name;

Jobs with no email operator may still be monitored externally, but verify that. Old job owners can cause failures after account changes. Notification levels are decoded from SQL Agent values: never, success, failure, or completion. A shared DBA mailbox can be better than one person if someone actually watches it.

How to check Database Mail profiles

Lists Database Mail profiles and accounts configured in msdb.

SELECT
    p.name AS profile_name,
    p.description,
    pp.is_default,
    a.name AS account_name,
    a.email_address
FROM msdb.dbo.sysmail_profile AS p
LEFT JOIN msdb.dbo.sysmail_principalprofile AS pp
    ON pp.profile_id = p.profile_id
LEFT JOIN msdb.dbo.sysmail_profileaccount AS pa
    ON pa.profile_id = p.profile_id
LEFT JOIN msdb.dbo.sysmail_account AS a
    ON a.account_id = pa.account_id
ORDER BY p.name, a.name;

No rows may mean Database Mail is not configured. A configured profile does not confirm email delivery works. Send a controlled test through approved procedures before relying on alerts. Check whether alerts go to a monitored address, not a stale mailbox.

What not to change in maintenance jobs

Do not delete old backup files until retention, restore testing, and legal or business requirements are understood.

Do not disable old SQL Agent jobs until dependencies, owners, schedules, and downstream systems are mapped.

Do not rebuild every index as a tuning strategy; check workload, statistics, duration, log growth, and blocking impact.

Do not treat green SQL Agent history as backup coverage, CHECKDB coverage, or alerting coverage.

Do not change schedules without checking job runtime, business load, ETL timing, and HA/DR role behavior.

Do not clean up msdb history so aggressively that job failures, restore history, and backup patterns disappear.

When to request a SQL Server maintenance review

Need a second pair of eyes on SQL Server maintenance?

If the jobs exist but nobody fully trusts them, it is worth checking what runs, what fails, what is missing, and which changes are safe to make first.

A maintenance review is a good starting point when backups, CHECKDB, cleanup, index work, or SQL Agent alerts have not been looked at properly for a while.

Request maintenance review Read the health audit page

Good fit when

SQL Agent jobs are old, unclear, or partly disabled.
Backups run, but restore testing or restore history is weak.
CHECKDB, index work, or cleanup has not been reviewed in years.
Maintenance jobs fail quietly or run past the window.

Next step

Use the SQL Server health check guide if maintenance exposes wider SQL Server ownership, monitoring, or readiness problems.

Read the SQL Server backup guide when the maintenance review exposes weak restore testing, or the SQL Server monitoring guide when failures are not reaching the right people.