DBA Scripts: Get Suspect Pages and Integrity Checks

Pages Already Recorded as Bad, and a Backup Check Before DBCC CHECKDB

An 823 or 824 just landed in the error log, or DBCC CHECKDB is due and you want to know whether anything is already recorded against this server. Get-SuspectPages lists the pages SQL Server has already flagged as bad, and any row with event_type 1, 2 or 3 is the one to check first. Get-DatabaseIntegrityChecks shows each database’s last backups, recovery model and page verify setting, so you know you have a restore to fall back on before the check runs.

What a Row in suspect_pages Means

• A row with event_type 1, 2 or 3 is a page to check now. 4, 5 and 7 are history. SQL Server writes the row when it hits an 823 or 824 on a page: a bad checksum, a torn page or an I/O error. MS Docs notes some rows come from transient errors, so a row means check the page, not proof it is still bad.
• A restore does not clear the row. It changes event_type to 4, a repair sets 5 or 7, and the row stays until someone deletes it. Read event_type before you panic over an old row.

When to Run These Scripts

• Get-SuspectPages, the moment an 823 or 824 shows up in the error log, and as a routine health check (it’s part of the standard health-check suite)
• Get-DatabaseIntegrityChecks, before a manual DBCC CHECKDB, to confirm there is a backup and the recovery model you expect
• A Get-SuspectPages row with event_type 1, 2 or 3 means DBCC CHECKDB on that database now
• Alongside Last DBCC CHECKDB and the rest of the Backups and Recovery scripts

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
*/
-- 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
*/
-- 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:

• sql/monitoring/databases/Get-SuspectPages.sql
• sql/monitoring/databases/Get-DatabaseIntegrityChecks.sql

Example Output

Get-SuspectPages

I inserted this row by hand against a real data page of a lab table, rather than corrupting a file. MS Docs allows a DBA to add rows to msdb.dbo.suspect_pages.

A full restore of Lab1005_sp_db afterwards turned this row into event_type 4, Restored, and kept it. It is history now, and it stays until someone deletes it.

Get-DatabaseIntegrityChecks

5 of the 64 rows, trimmed to the columns that differ.

• Lab1005_sp_reuse is the trap. It was dropped and created again, has never been backed up since, and still shows a full backup: the dropped database’s. Backup history is matched by name.
• Lab1005_sp_old needs 2 fixes before a check: no backup to fall back on, and TORN_PAGE_DETECTION instead of CHECKSUM.
• Lab1005_sp_rst is a restored copy, so it shows no backups here even though its source has them.

Understanding the Results

Best Practices

• Treat any row with event_type 1, 2 or 3 as urgent. Delete old 4, 5 and 7 rows so the table never reaches its 1,000-row limit
• Run Get-DatabaseIntegrityChecks before every manual DBCC CHECKDB. A recent backup is cheap insurance against a check that finds something serious
• Set PAGE_VERIFY CHECKSUM on every database that isn’t already using it
• Pair it with Last DBCC CHECKDB: this page tells you what has been recorded, that one tells you when the database was last actually checked

MS Docs covers DBCC CHECKDB, sys.databases, the suspect_pages table and managing it in full.

Related Scripts

You may also find these scripts useful:

• Backups and Recovery (hub)
• Last DBCC CHECKDB
• Errors 823 and 824, the incident page for a row with event_type 1, 2 or 3
• DBCC CHECKDB Found Corruption, what to do once the check confirms it
• Backup Coverage
• DBA Scripts: Get Database Health
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

msdb.dbo.suspect_pages tells you what SQL Server has already caught. The pre-check tells you whether you have a backup in hand before DBCC CHECKDB looks for more.

Run Get-SuspectPages as a routine health check and the moment an 823 or 824 appears. Run Get-DatabaseIntegrityChecks before every manual DBCC CHECKDB.