DBA Scripts: Get Suspect Pages and Integrity Checks

April 19, 2026

–

Part of the DBA-Tools Project.

Corruption Evidence Already Recorded, and a Pre-Check Before You Look Deeper

Last DBCC CHECKDB tells you how stale your corruption checks are. These two scripts sit on either side of that: Get-SuspectPages surfaces pages SQL Server has already flagged as corrupt, evidence sitting in msdb.dbo.suspect_pages right now, not something you need to go looking for. Get-DatabaseIntegrityChecks is the pre-check to run before a manual DBCC CHECKDB, database state, recovery model, and backup history in one row so you know exactly what you’re about to check and whether a recent backup exists first.

Why Suspect Pages and Integrity Pre-Checks Matter

msdb.dbo.suspect_pages is populated automatically the moment SQL Server detects a torn page, bad checksum, or I/O error, a row here is confirmed evidence, not a maybe
Entries persist until manually cleared or the database is restored clean, an old-looking last_update_date doesn’t mean the problem went away on its own
Running DBCC CHECKDB without knowing the current backup situation first is a real risk, if it finds something that needs REPAIR_ALLOW_DATA_LOSS, you want a recent backup already in hand, not a scramble to get one
page_verify_option_desc should be CHECKSUM on every modern database; anything else (TORN_PAGE_DETECTION, NONE) means corruption detection itself is weaker than it should be

When to Run These Scripts

Get-SuspectPages — as a routine health check (it’s part of the standard health-check suite), and immediately if I/O errors show up in the error log
Get-DatabaseIntegrityChecks — before running DBCC CHECKDB manually, to confirm backup coverage and recovery model first
Any row from Get-SuspectPages should trigger an immediate DBCC CHECKDB on the affected database
Alongside Last DBCC CHECKDB and the rest of the Backups and Recovery cluster

The Scripts

1. Get-SuspectPages — Recorded Corruption Evidence

/*
Script Name : Get-SuspectPages
Category    : maintenance-and-reliability
Purpose     : Show any pages recorded in msdb.dbo.suspect_pages, evidence of I/O or
              corruption errors.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-suspect-pages-and-integrity-checks/)
Requires    : db_datareader on msdb
Notes       : Any rows here indicate a serious integrity concern. Cross-reference with
              the error log and run DBCC CHECKDB immediately on the affected database.
              Entries persist until manually cleared or the database is restored clean.
HealthCheck : Yes
*/
-- Blog: https://sqldba.blog/dba-scripts-get-suspect-pages-and-integrity-checks/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    DB_NAME(sp.database_id)                                         AS database_name,
    sp.file_id,
    sp.page_id,
    CASE sp.event_type
        WHEN 1 THEN '823/824 hard I/O error'
        WHEN 2 THEN 'Bad checksum'
        WHEN 3 THEN 'Torn page'
        WHEN 4 THEN 'Restored (no longer suspect)'
        WHEN 5 THEN 'Repaired by DBCC'
        WHEN 7 THEN 'Deallocated by DBCC CHECKDB'
        ELSE        CAST(sp.event_type AS VARCHAR(10))
    END                                                             AS event_type,
    sp.error_count,
    sp.last_update_date
FROM msdb.dbo.suspect_pages AS sp
ORDER BY sp.last_update_date DESC;

2. Get-DatabaseIntegrityChecks — Pre-Check Before Running DBCC CHECKDB

/*
Script Name : Get-DatabaseIntegrityChecks
Category    : maintenance-and-reliability
Purpose     : Pre-check database readiness and configuration for integrity validation runs.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-suspect-pages-and-integrity-checks/)
Requires    : VIEW ANY DATABASE, db_datareader on msdb
*/
-- Blog: https://sqldba.blog/dba-scripts-get-suspect-pages-and-integrity-checks/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- Use as a pre-check before running DBCC CHECKDB.

SELECT
    d.name AS database_name,
    d.state_desc,
    d.recovery_model_desc,
    d.user_access_desc,
    d.is_read_only,
    d.is_auto_close_on,
    d.is_auto_shrink_on,
    d.page_verify_option_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_differential_backup,
    MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log_backup
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS bs
    ON d.name = bs.database_name
GROUP BY
    d.name,
    d.state_desc,
    d.recovery_model_desc,
    d.user_access_desc,
    d.is_read_only,
    d.is_auto_close_on,
    d.is_auto_shrink_on,
    d.page_verify_option_desc
ORDER BY d.name;

/*
Example manual integrity run in SSMS:

DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

For a faster targeted check on one database:

DBCC CHECKDB ('YourDatabaseName') WITH TABLOCK, PHYSICAL_ONLY, NO_INFOMSGS;
*/

How To Run From The Repo

Clone DBA Tools, initialize and run whichever script answers your question:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check for recorded corruption evidence:
.\run.ps1 Get-SuspectPages

# Pre-check database state and backup coverage before DBCC CHECKDB:
.\run.ps1 Get-DatabaseIntegrityChecks

# Either against a remote sql server:
.\run.ps1 Get-DatabaseIntegrityChecks -ServerInstance SQLSERVER01

These scripts live in the repo at:

Example Output

Real output from this lab instance, not staged. Get-SuspectPages returns 0 rows, no recorded corruption evidence on this instance, an honest, clean result.

Get-DatabaseIntegrityChecks (24 rows, condensed here):

database_name	recovery_model_desc	page_verify_option_desc	last_full_backup
DemoDatabase	FULL	CHECKSUM	25/07/2026
GrowthLab	FULL	CHECKSUM	25/07/2026
master	SIMPLE	CHECKSUM	09/02/2026
migdb_37A45A06	FULL	CHECKSUM	(none)
migdb_7049216A	FULL	CHECKSUM	(none)

Two migration-lab databases show no recorded full backup at all, a real, valid pre-check finding: run a full backup before DBCC CHECKDB on either one, so a repair (if ever needed) has a recovery option that doesn’t depend on the check succeeding.

Understanding the Results

Any row from Get-SuspectPages — confirmed corruption evidence, cross-reference the error log for the original 823/824 error and run DBCC CHECKDB on the affected database immediately, don’t wait for a scheduled maintenance window
event_type = ‘Restored (no longer suspect)’ or ‘Repaired by DBCC’ — historical entries showing the issue was already addressed; still worth confirming when and how, since the row itself doesn’t get deleted automatically
last_full_backup = NULL — no recorded full backup exists for that database; get one before running DBCC CHECKDB, especially if a repair might be needed afterward
page_verify_option_desc ≠ CHECKSUM — weaker corruption detection than the modern default; change with ALTER DATABASE [name] SET PAGE_VERIFY CHECKSUM, existing pages get checksums as they’re next written, not retroactively

Best Practices

Treat any row in Get-SuspectPages as urgent, this is confirmed evidence, not a probabilistic warning
Run Get-DatabaseIntegrityChecks before every manual DBCC CHECKDB, confirming a recent backup exists is cheap insurance against a check that finds something serious
Set PAGE_VERIFY CHECKSUM on every database that isn’t already using it
Pair with Last DBCC CHECKDB to know both whether corruption has been recorded and how recently the database was actually checked for it

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

Does 0 rows from Get-SuspectPages guarantee no corruption exists?

No, it means no corruption has been detected and recorded yet. msdb.dbo.suspect_pages only populates when SQL Server actually encounters a checksum failure, torn page, or I/O error during normal operation or a DBCC CHECKDB run. A database that’s never had DBCC CHECKDB run against it and never happened to hit a bad page during normal reads could still have undetected corruption; this script reports evidence, not a guarantee of a clean database.

Why check backup history before running DBCC CHECKDB, a read-only operation?

DBCC CHECKDB itself is safe. The concern is what you do if it finds something: repairing corruption sometimes means REPAIR_ALLOW_DATA_LOSS, and having a recent, valid backup as an alternative to a lossy repair is the difference between a clean recovery and losing whatever the repair drops.

Summary

Corruption evidence and a pre-flight check for the tool that finds more of it are two different, complementary things. msdb.dbo.suspect_pages tells you what SQL Server has already caught; the integrity pre-check tells you whether you’re actually ready to run DBCC CHECKDB with a safety net in place if it finds more.

Run Get-SuspectPages as a routine health check, and use Get-DatabaseIntegrityChecks every time before a manual DBCC CHECKDB, confirming backup coverage first is a cheap step that matters exactly when you need it most.

Database Maintenance Monitoring

Blog Categories

Comments

Leave a Reply Cancel reply