
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_pagesfrom 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
One thought on “Integrity Check Report: Proving CHECKDB Actually Ran”
Comments are closed.