Detect Suspect Database Pages with msdb.dbo.suspect_pages

Storage errors do not always surface as immediate failures. When SQL Server hits an 823 or 824 error while reading a database page, it records that page in msdb.dbo.suspect_pages — a system table in msdb that keeps one row per suspect page until someone deletes it. Querying this table is the fastest first pass in any corruption investigation: it shows whether bad pages exist and which databases hold them before you commit to a full DBCC CHECKDB run.

Purpose and Overview

msdb.dbo.suspect_pages is SQL Server's registry of pages that failed a read. Unlike an error log entry that needs text parsing, it holds structured data: one row per suspect page, with the database ID, file ID, page ID, error type, error count, and the time of the last update. The table lives in msdb, so its rows persist until a DBA removes them. SQL Server only deletes rows on its own when a file is removed with ALTER DATABASE ... REMOVE FILE or the database is dropped. A page flagged months ago and never cleaned up is still listed. Microsoft also notes that a listed page is only suspected of being bad and might actually be fine — the table records transient errors too, such as a disconnected cable or a page that briefly fails a repeated checksum test.

The event_type column says what kind of failure was recorded. Event type 1 is an 823 error caused by an operating-system CRC error, such as a disk error, or an 824 error other than a bad checksum or a torn page — a bad page ID, for example. Event type 2 is a bad checksum. Event type 3 is a torn page. These three are the active corruption signals. Event types 4, 5, and 7 mark pages that were later restored from a backup, repaired by DBCC, or deallocated by DBCC.

The error_count column counts how many times the error has occurred on that page. One row with a count of 1 can be a transient fault. A page that keeps failing, or a sudden burst of new rows across many pages, points at the I/O subsystem — Microsoft's own guidance is that a sudden increase in rows being added is a reason to investigate the storage path. For a DBA triaging a production alert, those two signals separate the "watch it" cases from the "plan the restore now" cases.

One operational boundary matters more than any other: the table is limited to 1,000 rows, and if it fills, new errors are not logged in it. A full suspect_pages table is a serious signal on its own. It means corruption events are arriving faster than anyone is cleaning up, and new bad pages are going unrecorded. Microsoft recommends that DBAs periodically delete or archive rows marked restored or repaired, and rows with an old last_update_date, so the table never reaches that limit.

Code Breakdown

The detection script queries the table for all active (unresolved) suspect pages, joins to sys.master_files for the physical file path, and decodes the event_type integer into a readable description.

 1USE msdb;
 2GO
 3
 4SELECT
 5    DB_NAME(sp.database_id)             AS database_name,
 6    mf.physical_name                    AS file_path,
 7    sp.file_id,
 8    sp.page_id,
 9    CASE sp.event_type
10        WHEN 1 THEN '823 error or nonspecific 824 error'
11        WHEN 2 THEN 'Bad checksum (824)'
12        WHEN 3 THEN 'Torn page (824)'
13        WHEN 4 THEN 'Restored from backup'
14        WHEN 5 THEN 'Repaired by DBCC'
15        WHEN 7 THEN 'Deallocated by DBCC'
16        ELSE        'Unknown event type'
17    END                                 AS event_type_desc,
18    sp.error_count,
19    sp.last_update_date
20FROM msdb.dbo.suspect_pages AS sp
21LEFT JOIN sys.master_files AS mf
22    ON  sp.database_id = mf.database_id
23    AND sp.file_id     = mf.file_id
24WHERE sp.event_type IN (1, 2, 3)
25ORDER BY sp.last_update_date DESC;

To view all entries including resolved pages — useful for a historical audit of prior repair activity:

1SELECT
2    DB_NAME(sp.database_id)             AS database_name,
3    sp.file_id,
4    sp.page_id,
5    sp.event_type,
6    sp.error_count,
7    sp.last_update_date
8FROM msdb.dbo.suspect_pages AS sp
9ORDER BY sp.last_update_date DESC;

Running in the msdb context

suspect_pages is a system table in the msdb database, not in each user database. Page failures from every database on the instance funnel into this one table — there is no per-database equivalent. The script names the table with its three-part name, msdb.dbo.suspect_pages, so it runs from any database; the USE msdb line only makes the context explicit. On permissions, Microsoft's rule is simple: anyone with access to msdb can read the table. Updating a row needs UPDATE permission on it, and inserting or deleting rows needs membership in the db_owner role in msdb or the sysadmin fixed server role.

Filtering on event_type IN (1, 2, 3)

The WHERE event_type IN (1, 2, 3) predicate keeps the three active error categories and drops rows for pages that have already been dealt with. This is the same filter Microsoft uses in its own example query for returning the bad pages in the table. Without it, the result set mixes current problems with historical entries for pages that were restored, repaired, or deallocated. In a post-incident review, dropping the filter and reading the full history is useful; in a monitoring or alerting context, the filter keeps the result focused on pages that still need attention.

Joining to sys.master_files for physical paths

msdb.dbo.suspect_pages stores only integer IDs — database_id, file_id, and page_id. The join to sys.master_files resolves those IDs to a physical_name, the full path of the affected data or log file. That helps with a storage-team handoff: the output names not just the database but the actual file, which points to the drive, LUN, or SAN volume involved. sys.master_files is a single, server-wide view with a row for every file of every database stored in master, and it lists files in every state — including OFFLINE, RESTORING, and SUSPECT. The LEFT JOIN rather than an INNER JOIN keeps a suspect-page row even when its IDs no longer match any file the instance knows about, such as a database that has since been detached.

Decoding event_type

The CASE expression translates the integer event_type into a readable label, using the values Microsoft documents. Type 1 is the broad category: an 823 error from an operating-system CRC failure, or an 824 error that is neither a bad checksum nor a torn page. Type 2 is a bad checksum and type 3 is a torn page — both are 824 errors, but they are separate codes, and the distinction matters when you read the output. Type 4 means the page was restored; a full, file, or page RESTORE writes it. Type 5 means DBCC repaired the page — DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS sets it, and a DBCC check also marks listed pages it finds error-free as repaired. Type 7 means DBCC deallocated the page. Types 4, 5, and 7 record prior resolution and are left out of the active-errors query by the event_type IN (1, 2, 3) filter.

Table capacity monitoring

A companion query that shows how close the table is to its 1,000-row limit:

1SELECT
2    COUNT(*)                  AS total_rows,
3    1000 - COUNT(*)           AS rows_remaining,
4    MAX(last_update_date)     AS most_recent_event
5FROM msdb.dbo.suspect_pages;

When total_rows approaches 1,000, either the corruption problem is active and widening or old rows have never been cleaned out. Either way, act before it fills: once the table is full, new errors are not logged in it, and the detection script above starts missing bad pages.

Key Benefits and Use Cases

  • Instant corruption visibility — a single query surfaces all unresolved suspect pages across every database on the instance without running a full DBCC CHECKDB.
  • Repeated-failure identification — error_count shows how many times the error has occurred on each page, so a page that keeps failing stands out from a one-off transient error.
  • Storage-team handoff — joining to sys.master_files maps the database and file IDs to a physical path, so storage investigation starts at the right file.
  • Repair audit trail — the resolved event types (4, 5, 7) are set by restores and DBCC operations, giving a record of what was fixed and how.
  • Capacity alerting — tracking total row count catches the approach to the 1,000-row limit, after which new errors are not recorded in the table.
  • Lightweight baseline — the detection query is cheap on a clean instance and only returns rows when suspect pages actually exist.

Performance Considerations

  • Three-part name outside msdb: when the query runs from a user database, it must reference msdb.dbo.suspect_pages in full; a bare suspect_pages only resolves inside msdb.
  • 1,000-row limit: the table cannot grow past 1,000 rows. When it is full, new errors are not logged in it, so routine cleanup of resolved rows is what keeps the monitoring working.
  • Resolved rows are not purged automatically: SQL Server updates error_count and last_update_date when a listed page fails again, and changes event_type after a restore or repair, but it only deletes rows on ALTER DATABASE ... REMOVE FILE or DROP DATABASE. Rows with event types 4, 5, and 7 accumulate until a DBA deletes them — Microsoft's own example is DELETE FROM msdb..suspect_pages WHERE (event_type = 4 OR event_type = 5 OR event_type = 7).
  • What a NULL file path means: sys.master_files lists files for databases in every state, including offline and suspect ones, so a NULL physical_name means the row's database and file IDs no longer match an attached database file — not that the database is offline.
  • No alert by default: SQL Server does not send a notification when a row is added. Poll the table with a SQL Server Agent job, or watch the Database Suspect Data Page event class, which Microsoft names as the way to monitor activity on this table.

Practical Tips

  • Schedule a SQL Server Agent job that queries suspect_pages WHERE event_type IN (1, 2, 3) and sends a Database Mail notification when any rows come back — without active polling, new entries accumulate with no one watching.
  • After a page has been restored or repaired, delete or archive its row so the active list stays accurate and the table keeps room for new errors. Microsoft also allows a DBA to update a row — for example, to keep a record for a page known to be intact.
  • Run DBCC CHECKDB against the affected database before choosing between a restore and a repair. It confirms what is damaged and which objects own the flagged pages; REPAIR_ALLOW_DATA_LOSS can, as its name says, discard data, so a restore from a clean backup is usually the safer path.
  • If many new rows appear in a short time, treat it as a storage problem first, as Microsoft advises: investigate the I/O subsystem before attempting repeated repairs.
  • Cross-reference flagged database_id values against your backup schedule straight away — a database with active suspect pages and no recent clean backup is at acute risk. Confirm a restore point exists before running any repair.

Conclusion

msdb.dbo.suspect_pages is the most direct window into page-level read failures across a SQL Server instance: a structured, queryable table that records each suspect page, keeps it until a DBA removes it, and counts how often each page has failed. Querying it with the detection script above — filtering to the active event types and joining to sys.master_files for physical file paths — is the fast first pass every corruption-response workflow should start with. Paired with a scheduled Agent job that alerts on new rows and routine cleanup of resolved ones, it closes the monitoring gap that lets page corruption build up unnoticed.

References

Posts in this series