Integrity Check Report: Proving CHECKDB Actually Ran

SQL Server Auditing: Integrity Check Report: Proving CHECKDB Actually Ran

Applies to SQL Server 2019, 2022 and 2025. The script runs in Windows PowerShell 5.1 or PowerShell 7.

Most estates have a weekly DBCC CHECKDB job. Far fewer can say, for every database on every server, when it last passed. Jobs get disabled during an incident and never turned back on, new databases get missed, and a job that times out every week looks fine unless someone reads the history. This report doesn’t run CHECKDB. It checks that your existing jobs are doing their job, and it reads the one table that records corruption SQL Server has already found.

What the report shows

  • Last good CHECKDB per database. The date SQL Server itself records when DBCC CHECKDB finishes with no errors, read with DATABASEPROPERTYEX(name, 'LastGoodCheckDbTime'). Each database is marked OK, OVERDUE or NEVER CHECKED.
  • Availability group replicas. If a database is in an availability group and you only check one replica, the secondary shows the newest check on any replica rather than a false alarm.
  • The integrity jobs on each server. Any Agent job whose name or step mentions CHECKDB or DatabaseIntegrityCheck: enabled or not, last run, outcome, how long it took, next run. Servers with no such job at all are listed too.
  • Suspect pages. Every row in msdb.dbo.suspect_pages from the last 90 days: 823 and 824 errors, bad checksums and torn pages.
  • Servers the audit couldn’t reach, so a missing server never looks like a clean one.

Why it matters

Corruption is rare, but when it happens the question is always the same: when was the last clean check? That date decides which backup you can trust. If the answer is “three months ago, the job was disabled in August”, every backup since then is suspect too.

The suspect pages table is the early warning. SQL Server writes to it when a read hits a bad page, long before the weekly check runs. Any row there needs looking at the same day, and the storage team needs to hear about it.

Run it daily, after the integrity jobs. The DBA reads the exceptions. The weekly summary for the IT lead is the count of databases not checked in 8 days, which should be zero.

The script

For each server, it runs one query that returns three result sets (databases, integrity jobs and suspect pages) and stores them with the same capture date, so each run can be compared with the last. It reads only: nothing runs CHECKDB, changes a job or touches a user database.

# Invoke-IntegrityCheckAudit.ps1
# Checks that your existing DBCC CHECKDB jobs are doing their job. For every
# server it records:
#   - when each database last passed CHECKDB (LastGoodCheckDbTime)
#   - the CHECKDB / DatabaseIntegrityCheck Agent jobs: last run, outcome, next run
#   - any suspect pages logged in msdb in the last 90 days
# It doesn't run CHECKDB itself. Schedule it daily, after the integrity jobs.
#
# Tables: run sql\01_Create_Tables.sql first.
# Report: sql\IntegrityCheckReport.sql

# ---- Settings ---------------------------------------------------------------
$ServerListPath    = 'C:\DBA\Servers.txt'
$InventoryServer   = 'SQLMON01'
$InventoryDatabase = 'DBA'
# -----------------------------------------------------------------------------

. "$PSScriptRoot\DbaReportHelpers.ps1"

# Three result sets: databases, integrity jobs, suspect pages
$IntegrityQuery = @"
SET NOCOUNT ON;

SELECT
    d.name                                                         AS DatabaseName,
    d.state_desc                                                   AS StateDesc,
    d.is_read_only                                                 AS IsReadOnly,
    CAST(CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS BIT)  AS IsInAG,
    rs.role_desc                                                   AS ReplicaRole,
    CASE WHEN d.state_desc = 'ONLINE' THEN
        NULLIF(CONVERT(DATETIME2(0), DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime')), '1900-01-01')
    END                                                            AS LastGoodCheckDb
FROM sys.databases AS d
LEFT JOIN sys.dm_hadr_availability_replica_states AS rs
       ON rs.replica_id = d.replica_id
      AND rs.is_local = 1
WHERE d.database_id <> 2;                       -- tempdb can't be checked meaningfully

SELECT
    j.name                                          AS JobName,
    CAST(j.enabled AS BIT)                          AS IsEnabled,
    l.LastRunTime,
    CASE l.run_status WHEN 0 THEN 'Failed'    WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry'
                      WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'In progress' END AS LastRunOutcome,
    CAST(((l.run_duration / 10000) * 3600
        + (l.run_duration / 100 % 100) * 60
        +  l.run_duration % 100) / 60.0 AS DECIMAL(9,1)) AS LastRunDurationMin,
    n.NextRunTime,
    LEFT(l.message, 4000)                           AS LastMessage
FROM msdb.dbo.sysjobs AS j
OUTER APPLY
(
    SELECT TOP (1)
        msdb.dbo.agent_datetime(h.run_date, h.run_time) AS LastRunTime,
        h.run_status, h.run_duration, h.message
    FROM msdb.dbo.sysjobhistory AS h
    WHERE h.job_id = j.job_id
      AND h.step_id = 0                            -- the job outcome row
    ORDER BY h.instance_id DESC
) AS l
OUTER APPLY
(
    SELECT MIN(msdb.dbo.agent_datetime(s.next_run_date, s.next_run_time)) AS NextRunTime
    FROM msdb.dbo.sysjobschedules AS s
    WHERE s.job_id = j.job_id
      AND s.next_run_date > 0
) AS n
WHERE j.name LIKE N'%CHECKDB%'
   OR j.name LIKE N'%integrity%'
   OR EXISTS (SELECT 1 FROM msdb.dbo.sysjobsteps AS st
              WHERE st.job_id = j.job_id
                AND (st.command LIKE N'%CHECKDB%' OR st.command LIKE N'%DatabaseIntegrityCheck%'));

SELECT
    DB_NAME(database_id)  AS DatabaseName,
    file_id               AS FileId,
    page_id               AS PageId,
    CASE event_type WHEN 1 THEN '823/824 error' WHEN 2 THEN 'Bad checksum' WHEN 3 THEN 'Torn page'
                    WHEN 4 THEN 'Restored'      WHEN 5 THEN 'Repaired'     WHEN 7 THEN 'Deallocated' END AS EventType,
    error_count           AS ErrorCount,
    last_update_date      AS LastUpdateDate
FROM msdb.dbo.suspect_pages
WHERE last_update_date >= DATEADD(DAY, -90, GETDATE());
"@

$Servers     = Get-ServerList -Path $ServerListPath
$CaptureDate = Get-Date
$Repo        = Open-SqlConnection -Server $InventoryServer -Database $InventoryDatabase

try {
    Assert-InventoryTable $Repo 'dbo.CollectionLog', 'dbo.IntegrityCheckHistory', 'dbo.IntegrityJobHistory', 'dbo.SuspectPage'

    foreach ($Server in $Servers) {
        try {
            $Source = Open-SqlConnection -Server $Server
            try {
                $Set = Invoke-SqlQuerySet $Source $IntegrityQuery
            }
            finally {
                $Source.Close()
            }

            $Databases = Add-DataTableRows $Repo 'dbo.IntegrityCheckHistory' $Set.Tables[0] $Server $CaptureDate
            $Jobs      = Add-DataTableRows $Repo 'dbo.IntegrityJobHistory'   $Set.Tables[1] $Server $CaptureDate
            $Pages     = Add-DataTableRows $Repo 'dbo.SuspectPage'           $Set.Tables[2] $Server $CaptureDate

            Write-CollectionLog $Repo -Collection 'Integrity' -ServerName $Server -CaptureDate $CaptureDate `
                                -Status 'SUCCESS' -Rows $Databases
            Write-Host "$Server : $Databases databases, $Jobs integrity jobs, $Pages suspect pages"
        }
        catch {
            Write-CollectionLog $Repo -Collection 'Integrity' -ServerName $Server -CaptureDate $CaptureDate `
                                -Status 'FAILED' -ErrorMessage $_.Exception.Message
            Write-Warning "$Server : $($_.Exception.Message)"
        }
    }
}
finally {
    $Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script). This version adds the functions the newer collectors use; it replaces the older copy and the earlier scripts still work with it.
# DbaReportHelpers.ps1
# Shared helpers for the auditing scripts. Save it next to them and dot-source it:
#     . "$PSScriptRoot\DbaReportHelpers.ps1"
# Windows Authentication throughout: run the scripts as a service account that
# can reach every server. No passwords live in any of these files.

function Get-ServerList {
    # One server per line. Blank lines and anything after # are ignored,
    # and duplicates (in any case) are removed.
    param([Parameter(Mandatory)][string]$Path)

    Get-Content -Path $Path |
        ForEach-Object { ($_ -split '#')[0].Trim() } |
        Where-Object { $_ } |
        Sort-Object -Unique
}

function Get-ComputerName {
    # SERVER\INSTANCE -> SERVER, for the Windows calls (CIM, ping, remoting)
    param([Parameter(Mandatory)][string]$Server)
    ($Server -split '\\')[0]
}

function Open-SqlConnection {
    param(
        [Parameter(Mandatory)][string]$Server,
        [string]$Database = 'master'
    )

    $Conn = New-Object System.Data.SqlClient.SqlConnection
    $Conn.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;" +
                             "TrustServerCertificate=True;Connect Timeout=15;Application Name=DBA reports"
    $Conn.Open()
    $Conn
}

function Invoke-SqlQuery {
    # Runs a query and returns a DataTable (the leading comma stops PowerShell unrolling it).
    param($Connection, [string]$Sql, [hashtable]$Parameters = @{})

    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = $Sql
    $Cmd.CommandTimeout = 300
    foreach ($Name in $Parameters.Keys) {
        $null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
    }
    $Table = New-Object System.Data.DataTable
    $null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Table)
    , $Table
}

function Invoke-SqlNonQuery {
    param($Connection, [string]$Sql)

    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = $Sql
    $Cmd.CommandTimeout = 300
    $null = $Cmd.ExecuteNonQuery()
}

function Add-InventoryRow {
    # Inserts one row from an ordered hashtable of column = value.
    # Every value goes in as a parameter, never pasted into the SQL text.
    param(
        $Connection,
        [Parameter(Mandatory)][string]$Table,
        [Parameter(Mandatory)][System.Collections.IDictionary]$Row
    )

    $Columns = @($Row.Keys)
    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = "INSERT INTO $Table (" + ($Columns -join ', ') + ') VALUES (' +
                       (($Columns | ForEach-Object { "@$_" }) -join ', ') + ');'

    foreach ($Column in $Columns) {
        $Value = $Row[$Column]
        if ($null -eq $Value -or $Value -is [System.DBNull] -or
            ($Value -is [string] -and $Value.Trim() -eq '')) {
            $Value = [System.DBNull]::Value
        }
        $null = $Cmd.Parameters.AddWithValue("@$Column", $Value)
    }
    $null = $Cmd.ExecuteNonQuery()
}

function Invoke-SqlQuerySet {
    # Like Invoke-SqlQuery, but returns a DataSet: one DataTable per result set.
    param($Connection, [string]$Sql, [hashtable]$Parameters = @{})

    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = $Sql
    $Cmd.CommandTimeout = 600
    foreach ($Name in $Parameters.Keys) {
        $null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
    }
    $Set = New-Object System.Data.DataSet
    $null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Set)
    $Set
}

function Assert-InventoryTable {
    # Stops the run early with a clear message if 01_Create_Tables.sql hasn't been run.
    param($Connection, [Parameter(Mandatory)][string[]]$Table)

    foreach ($Name in $Table) {
        $Id = (Invoke-SqlQuery $Connection "SELECT OBJECT_ID(@Name, 'U') AS Id" @{ Name = $Name }).Rows[0].Id
        if ($Id -is [System.DBNull]) {
            throw "Table $Name is missing. Run sql\01_Create_Tables.sql in the DBA database first."
        }
    }
}

function Add-DataTableRows {
    # Inserts every row of a DataTable, adding the run's CaptureDate and ServerName. Returns the row count.
    param(
        $Connection,
        [Parameter(Mandatory)][string]$Table,
        [Parameter(Mandatory)]$Data,
        [Parameter(Mandatory)][string]$ServerName,
        [Parameter(Mandatory)][datetime]$CaptureDate
    )

    foreach ($Source in $Data.Rows) {
        $Row = [ordered]@{ CaptureDate = $CaptureDate; ServerName = $ServerName }
        foreach ($Column in $Data.Columns.ColumnName) {
            $Row[$Column] = $Source[$Column]
        }
        Add-InventoryRow -Connection $Connection -Table $Table -Row $Row
    }
    $Data.Rows.Count
}

function Write-CollectionLog {
    # One row per server per collection per run, so a server that failed is never just missing.
    param(
        $Connection,
        [Parameter(Mandatory)][string]$Collection,
        [Parameter(Mandatory)][string]$ServerName,
        [Parameter(Mandatory)][datetime]$CaptureDate,
        [Parameter(Mandatory)][ValidateSet('SUCCESS', 'PARTIAL', 'FAILED')][string]$Status,
        [int]$Rows = 0,
        [string]$ErrorMessage
    )

    Add-InventoryRow -Connection $Connection -Table 'dbo.CollectionLog' -Row ([ordered]@{
        CaptureDate      = $CaptureDate
        Collection       = $Collection
        ServerName       = $ServerName
        CollectionStatus = $Status
        RowsCollected    = $Rows
        ErrorMessage     = $ErrorMessage
    })
}

The tables

The script stops with a clear message if these are missing. Run this once before the first collection.

-- Run once in the DBA database on the inventory server, before the first run of
-- the script. Safe to run again: each table is only created if it's missing.
IF DB_ID(N'DBA') IS NULL
    CREATE DATABASE DBA;
GO

USE DBA;
GO

-- Shared by the collectors: one row per server per run, so a server that
-- failed is never just missing from the report
IF OBJECT_ID(N'dbo.CollectionLog', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.CollectionLog
    (
        CollectionLogID  BIGINT IDENTITY(1,1) NOT NULL
                         CONSTRAINT PK_CollectionLog PRIMARY KEY NONCLUSTERED,
        CaptureDate      DATETIME2(0)  NOT NULL,
        Collection       VARCHAR(30)   NOT NULL,   -- Integrity, WindowsPatch, SqlPatch, Security, Capacity
        ServerName       NVARCHAR(128) NOT NULL,
        CollectionStatus VARCHAR(20)   NOT NULL,   -- SUCCESS, PARTIAL, FAILED
        RowsCollected    INT           NULL,
        ErrorMessage     NVARCHAR(MAX) NULL
    );
    CREATE CLUSTERED INDEX CX_CollectionLog
        ON dbo.CollectionLog (Collection, ServerName, CaptureDate);
END;
GO

IF OBJECT_ID(N'dbo.IntegrityCheckHistory', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.IntegrityCheckHistory
    (
        CaptureDate     DATETIME2(0)  NOT NULL,
        ServerName      NVARCHAR(128) NOT NULL,
        DatabaseName    NVARCHAR(128) NOT NULL,
        StateDesc       NVARCHAR(60)  NULL,
        IsReadOnly      BIT           NULL,
        IsInAG          BIT           NULL,
        ReplicaRole     NVARCHAR(60)  NULL,        -- PRIMARY / SECONDARY, NULL if not in an AG
        LastGoodCheckDb DATETIME2(0)  NULL         -- NULL = never checked (or not readable)
    );
    CREATE CLUSTERED INDEX CX_IntegrityCheckHistory
        ON dbo.IntegrityCheckHistory (ServerName, DatabaseName, CaptureDate);
END;
GO

IF OBJECT_ID(N'dbo.IntegrityJobHistory', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.IntegrityJobHistory
    (
        CaptureDate        DATETIME2(0)   NOT NULL,
        ServerName         NVARCHAR(128)  NOT NULL,
        JobName            NVARCHAR(128)  NOT NULL,
        IsEnabled          BIT            NULL,
        LastRunTime        DATETIME2(0)   NULL,
        LastRunOutcome     NVARCHAR(20)   NULL,     -- Succeeded, Failed, Retry, Cancelled, In progress
        LastRunDurationMin DECIMAL(9,1)   NULL,
        NextRunTime        DATETIME2(0)   NULL,     -- NULL = no schedule
        LastMessage        NVARCHAR(4000) NULL
    );
    CREATE CLUSTERED INDEX CX_IntegrityJobHistory
        ON dbo.IntegrityJobHistory (ServerName, CaptureDate);
END;
GO

IF OBJECT_ID(N'dbo.SuspectPage', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.SuspectPage
    (
        CaptureDate    DATETIME2(0)  NOT NULL,
        ServerName     NVARCHAR(128) NOT NULL,
        DatabaseName   NVARCHAR(128) NULL,
        FileId         INT           NOT NULL,
        PageId         BIGINT        NOT NULL,
        EventType      NVARCHAR(30)  NULL,
        ErrorCount     INT           NULL,
        LastUpdateDate DATETIME2(0)  NULL
    );
    CREATE CLUSTERED INDEX CX_SuspectPage
        ON dbo.SuspectPage (ServerName, CaptureDate);
END;
GO

The report queries

Run these in the DBA database. The first is the list that matters: anything not OK, worst first. @MaxDays is 8, a weekly check plus a day’s slack; change it if your checks run on a different cycle.

/*
IntegrityCheckReport.sql - run in the DBA database.
Weekly report on DBCC CHECKDB across the estate, from the latest run of
Invoke-IntegrityCheckAudit.ps1.
*/
USE DBA;

DECLARE @MaxDays INT = 8;     -- weekly CHECKDB plus a day's slack

/* 1. Databases not checked recently ------------------------------------- */
WITH LatestRun AS
(
    SELECT ServerName, MAX(CaptureDate) AS CaptureDate
    FROM dbo.CollectionLog
    WHERE Collection = 'Integrity' AND CollectionStatus = 'SUCCESS'
    GROUP BY ServerName
),
Db AS
(
    SELECT H.*
    FROM dbo.IntegrityCheckHistory AS H
    JOIN LatestRun AS R ON R.ServerName = H.ServerName AND R.CaptureDate = H.CaptureDate
),
AgBest AS
(
    -- An AG database may be checked on one replica only: show the newest check on any replica
    SELECT DatabaseName, MAX(LastGoodCheckDb) AS BestOnAnyReplica
    FROM Db
    WHERE IsInAG = 1
    GROUP BY DatabaseName
)
SELECT
    D.ServerName,
    D.DatabaseName,
    D.StateDesc,
    D.ReplicaRole,
    D.LastGoodCheckDb,
    DATEDIFF(DAY, D.LastGoodCheckDb, D.CaptureDate) AS DaysSinceCheck,
    A.BestOnAnyReplica,
    CASE
        WHEN D.StateDesc <> 'ONLINE'                                   THEN 'CHECK: ' + D.StateDesc
        WHEN D.ReplicaRole = 'SECONDARY' AND D.LastGoodCheckDb IS NULL
         AND A.BestOnAnyReplica >= DATEADD(DAY, -@MaxDays, D.CaptureDate) THEN 'OK (checked on another replica)'
        WHEN D.LastGoodCheckDb IS NULL                                 THEN 'NEVER CHECKED'
        WHEN D.LastGoodCheckDb < DATEADD(DAY, -@MaxDays, D.CaptureDate) THEN 'OVERDUE'
        ELSE 'OK'
    END AS IntegrityStatus
FROM Db AS D
LEFT JOIN AgBest AS A ON A.DatabaseName = D.DatabaseName AND D.IsInAG = 1
ORDER BY
    CASE WHEN D.LastGoodCheckDb IS NULL THEN 0 ELSE 1 END,
    D.LastGoodCheckDb,
    D.ServerName, D.DatabaseName;

/* 2. Integrity jobs that failed, are disabled, have no schedule, or are missing */
WITH LatestRun AS
(
    SELECT ServerName, MAX(CaptureDate) AS CaptureDate
    FROM dbo.CollectionLog
    WHERE Collection = 'Integrity' AND CollectionStatus = 'SUCCESS'
    GROUP BY ServerName
)
SELECT
    R.ServerName,
    J.JobName,
    J.IsEnabled,
    J.LastRunTime,
    J.LastRunOutcome,
    J.LastRunDurationMin,
    J.NextRunTime,
    CASE
        WHEN J.JobName IS NULL                                        THEN 'NO CHECKDB JOB FOUND'
        WHEN J.IsEnabled = 0                                          THEN 'JOB DISABLED'
        WHEN J.LastRunOutcome = 'Failed'                              THEN 'LAST RUN FAILED'
        WHEN J.NextRunTime IS NULL                                    THEN 'NO SCHEDULE'
        WHEN J.LastRunTime < DATEADD(DAY, -@MaxDays, R.CaptureDate)   THEN 'NOT RUN FOR ' + CAST(@MaxDays AS VARCHAR(5)) + '+ DAYS'
        ELSE 'OK'
    END AS JobStatus,
    LEFT(J.LastMessage, 300) AS LastMessage
FROM LatestRun AS R
LEFT JOIN dbo.IntegrityJobHistory AS J
       ON J.ServerName = R.ServerName AND J.CaptureDate = R.CaptureDate
ORDER BY CASE WHEN J.JobName IS NULL OR J.LastRunOutcome = 'Failed' OR J.IsEnabled = 0 THEN 0 ELSE 1 END,
         R.ServerName, J.JobName;

/* 3. Suspect pages logged in the last 90 days: every row needs investigating */
WITH LatestRun AS
(
    SELECT ServerName, MAX(CaptureDate) AS CaptureDate
    FROM dbo.CollectionLog
    WHERE Collection = 'Integrity' AND CollectionStatus = 'SUCCESS'
    GROUP BY ServerName
)
SELECT P.ServerName, P.DatabaseName, P.FileId, P.PageId, P.EventType, P.ErrorCount, P.LastUpdateDate
FROM dbo.SuspectPage AS P
JOIN LatestRun AS R ON R.ServerName = P.ServerName AND R.CaptureDate = P.CaptureDate
ORDER BY P.LastUpdateDate DESC;

/* 4. Servers the audit couldn't reach in the latest run */
SELECT L.ServerName, L.CaptureDate, L.ErrorMessage
FROM dbo.CollectionLog AS L
WHERE L.Collection = 'Integrity'
  AND L.CaptureDate = (SELECT MAX(CaptureDate) FROM dbo.CollectionLog WHERE Collection = 'Integrity')
  AND L.CollectionStatus <> 'SUCCESS';

Watch out for

  • Split checks. The last good date is set by a clean DBCC CHECKDB. If your job spreads the work across the week with CHECKTABLE and CHECKALLOC, the date may not move, and the report will say OVERDUE. Check what your job actually runs before trusting the status.
  • Secondaries prove the secondary. Each replica has its own copy of the data on its own storage. A clean check on a secondary says nothing about corruption on the primary. Offloading checks is fine; checking only one replica, forever, is a gap.
  • Read-only databases. SQL Server can’t write the date into a read-only database, so the last good date stays where it was. If a read-only database shows OVERDUE, that’s why.
  • suspect_pages fills up. It holds 1,000 rows and SQL Server stops adding to it when it’s full. Delete rows once each problem has been dealt with, and keep the history here instead.
  • Job history retention. If Agent history is trimmed hard, a weekly job’s last run may already have been purged by the time this runs, and the job shows no last run. Keep at least a couple of weeks.

Where it fits

This is part of my Auditing section, with the Backups report it pairs with: a backup you can’t restore cleanly isn’t much of a backup. Running and scheduling the checks themselves is covered in the SQL Server DBA Handbook.

Microsoft’s page on the suspect_pages table explains each event type and how to clear it.

The other Auditing reports: Licensing | Availability: ping and response time | Disk space alerts | Server inventory | Database inventory | Backups | SSIS errors | Windows patching | SQL Server patching | Security | Capacity

Clive Roberts DBA