Suspect Pages Report

Overview

When SQL Server cannot read a page correctly it writes a row into msdb..suspect_pages and carries on. Nobody is told. There is no alert, no red icon, and the only trace is a line in the error log that scrolls away.

The Suspect Pages report is the page that reads that table. It is short, it is usually empty, and on the day it is not empty it is the most important page in this product.

An empty page is the correct answer, and it is the one you should get on every healthy instance you look at. The report says so plainly rather than drawing an empty chart.

The Suspect Pages report
One row per damaged page. Most instances see this page empty for years.

Where to find it

An instance level report. Right-click the server → Instance Level Reports → Suspect Pages.

The page title reads Suspect Pages for <server name>.

Requirements

  • No minimum version. msdb..suspect_pages has been there since SQL Server 2005.
  • Access to msdb. Reading the table needs membership in db_owner on msdb, or sysadmin.

Nothing on this page changes anything. Every fix for a suspect page is a restore or a DBCC command with real consequences, and none of them belong behind a right-click menu.

Reading the chart

Horizontal bars, one per row, length is the error count and the label is the kind of event.

On this page the chart is mostly there to make the shape obvious: one long bar means a page that has been hit repeatedly, which is a page something is still trying to read.

Reading the grid

Column What it is
Database The database the page belongs to, resolved from the database ID.
DB ID The database ID as stored. Present because the name resolves to blank if the database has since been dropped.
Event What happened to the page. The six values are listed below.
Error Count How many times this page has been recorded.
File ID The file within the database.
Page ID The page number within that file.
Last Updated When the row was last written.

Rows are ordered by Last Updated, newest first, so whatever happened most recently is at the top.

The Event column

Event What it means
1. 823 or 824 or Torn Page An operating system error, a checksum failure or a torn page write, other than a bad checksum or torn page detected by the engine itself. The general case, and the most common.
2. Bad Checksum The page’s checksum did not match. The page changed after SQL Server wrote it, which is a storage problem rather than a SQL Server one.
3. Torn Page The page was written incompletely, typically a power loss or a controller fault part way through a write.
4. Restored The page was replaced by a restore. Good news.
5. Repaired (DBCC) DBCC CHECKDB with a repair option fixed it.
7. Deallocated (DBCC) DBCC CHECKDB with a repair option deallocated it. Data was lost.

Events 1, 2 and 3 are damage. Events 4, 5 and 7 are outcomes. A page that has both a damage row and a later restore or repair row has been dealt with; a page with only a damage row has not.

Event 7 deserves attention even years later. Deallocated means REPAIR_ALLOW_DATA_LOSS threw the page away to make the database consistent again. Somebody chose to lose whatever was on it, and it is worth knowing which table that was.

The suspect pages grid
Event tells you whether the row is damage or the outcome of dealing with it.

What to do when it is not empty

In this order, and do not skip the first step.

  1. Take a backup now if you can. A damaged database that is still running is a database you can still get data out of.
  2. Read the error log. Error Log classifies 823, 824 and 825 entries and shows the lines around them. Error 825 in particular is the one that says a read succeeded only after a retry, which is storage telling you it is about to fail.
  3. Run DBCC CHECKDB. The suspect pages table records pages the engine tripped over while working. CHECKDB is what tells you the real extent of it.
  4. Prefer a page level restore. With FULL recovery and an unbroken log chain you can restore the individual pages and lose nothing. Backup Status shows whether the chain is intact. REPAIR_ALLOW_DATA_LOSS is the option of last resort, and its name is honest.
  5. Look at the hardware. Bad checksums and torn pages are storage faults. The database is where you noticed it, not where it happened.

The table does not clean itself

Two things about msdb..suspect_pages are worth knowing before reading old rows.

It holds a maximum of 1000 rows. Once full, SQL Server stops adding to it, so on an instance with a genuinely failing disk the table can go quiet while the problem continues.

Nothing removes old rows automatically. A row from a fault that was fixed three years ago is still there, which is why Last Updated matters as much as the row itself. Clearing rows that have been dealt with is a manual DELETE against the table, and it is worth doing so the next person reading this page is looking at something current.

How to read the report

  1. Read Last Updated first. A row from last night and a row from 2019 are different conversations.
  2. Group by database. Several pages in one database points at one file or one volume.
  3. Read the Event values together. Damage rows with no matching restore or repair row are the unfinished ones.
  4. Read Error Count. A count above one means something keeps trying to read the page and keeps failing.
  5. Check whether the database still exists. A blank name with a DB ID still filled in means the database was dropped, and the row is history.

Common patterns

One row, event 2, error count 1, months ago. A single bad checksum that never recurred. Usually a transient storage glitch. Worth confirming CHECKDB has run clean since.

Several rows in one database over a few days. A failing disk or a failing controller. I/O by Drive and Disk Latency by Hour by Day usually show the same volume misbehaving.

Event 7 rows. Somebody ran REPAIR_ALLOW_DATA_LOSS at some point. Find out what was lost and whether it was ever reconciled.

A row for a database that is not on the instance any more. History. The database was dropped or restored elsewhere; the row stayed behind.

Where the data comes from

  • msdb..suspect_pages, joined to nothing, ordered by last_update_date descending.
  • DB_NAME() for the database name, which returns blank for a database that no longer exists.

Nothing is stored by this page, and nothing on it is cached. It is the table as it stands.

Report Why you would go there
Error Log The 823, 824 and 825 entries around the time each page failed.
Last DBCC CheckDB Known Good by Database When each database was last verified clean.
Backup Status Whether a page level restore is possible, which depends on the log chain.
I/O by Drive Which volume the failing file sits on, and how it is behaving.
Disk Space The volume itself.
Files Which file the reported File ID actually is.

Frequently asked questions

The report is empty. Is that right? Yes, and it is what you want. Most instances never record a suspect page.

Is a suspect page the same as a corrupt database? No. It is one page SQL Server could not read correctly. DBCC CHECKDB is what tells you whether the database as a whole is consistent.

Why is the Database column blank? The database ID no longer resolves, which means the database has been dropped or detached since the row was written. The DB ID column is there for exactly this case.

Can I delete rows from here? Not from this page. Clearing dealt-with rows is a manual DELETE against msdb..suspect_pages, and it is deliberately not automated.

Does a row disappear once the page is fixed? No. A restore or a repair adds a new row with event 4, 5 or 7. The original damage row stays.

Why does the error count keep going up? Because something keeps reading the page and keeps failing. That is a live problem, not a historical one.

Does the report tell me which table the page belongs to? No. The table records the file and page number only. DBCC PAGE or the output of DBCC CHECKDB is what turns that into an object.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

To prove you are not a robot: *