SQL Server Tracking Suspect Pages

Did you know that SQL Server tracks suspect pages and puts the details into the msdb database in a table called suspect_pages? Well it does, but the question I want answering is what happens if the page within the suspect pages table is fixed? Does it automatically get removed/updated or do we as the administrators have to do anything manually?

Let’s find out.

Here is a very basic setup. Using the following query, I will find the page ID I want to ruin. That being page 126.

 DBCC IND (N'ZoraDB', N'people', -1);


I then do some offset calculations and sprinkle some “magic dust”. Then I am ready to run CHECKDB.


Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:126) could not be processed.  See other errors for details. CHECKDB found 0 allocation errors and 2 consistency errors in table ‘people’ (object ID 245575913). CHECKDB found 0 allocation errors and 2 consistency errors in database ‘ZoraDB’. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ZoraDB).

It is time to check the suspect_pages table.

USE [msdb]

SELECT * FROM dbo.suspect_pages
ORDER BY last_update_date DESC


Event type 2 means a bad checksum.

Now to the main point of the blog post, let’s run repair (a last resort option) on the database and make sure the database is fit and ready for users then we will go and re-check the suspect table.




CHECKDB fixed 0 allocation errors and 2 consistency errors in database ‘ZoraDB’. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

BY THE WAY – It deleted my data. I knew it would because I used REPAIR_ALLOW_DATA_LOSS.

 Anyways, now look at the suspect table.


It does NOT get removed, but the event type gets updated from 2 to 7 where 7 means it was de-allocated by CHECKDB.

I went through the same tests then recovered from backup. Again only the event type gets updated to Event type = 4.


So it is down to us to maintain this table which is limited to 1000 rows. The data will remain within this table so just watch out for the event_type to really understand the status of the page.



3 thoughts on “SQL Server Tracking Suspect Pages

  1. Pingback: Rounding Up The Usual Suspects – Curated SQL

Leave a Reply to blobeater Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s