March Madness – SQL Server System Tables – suspect_pages

I am taking a bit of a right turn with this post and subsequent ones for this series. I am heading into the msdb database from here on out. I am doing this for two reasons: because there is less than a week left in the series and because these tables inside of the msdb are more important than any remaining system tables found in master or a user database.

So today we shift gears and turn right (this ain’t no NASCAR race) and start the home stretch of this series by looking at the msdb.suspect_pages table.

What is this table for?

Unlike the other posts in this series, this table is NOT available in SQL2000. It was first available in SQL2005, and I am fairly certain that most people today (six years later) have no idea about this table. There are two reasons for that: first is that most folks never root around in the system tables and second is that most folks never need to recover from corruption errors (or never bother and just do a full restore  from backup).

From the BOL definition for the suspect_pages table:

“Contains one row per page that failed with a minor 823 error or an 824 error. Pages are listed in this table because they are suspected of being bad, but they might actually be fine. When a suspect page is repaired, its status is updated in the event_type column.

The [suspect_pages] table, which has a limit of 1,000 rows, is stored in the msdb database.”

So there you go, if you have a data page identified with either an 823 or 824 error then they get listed in this table. But only the first 1,000, and frankly if you have more errors than that then you have bigger problems to worry about.

Why should you care?

You care about the type of error, which can be found by looking at the event_type column. This column has the following values (from BOL):

1 = An 823 error that causes a suspect page (such as a disk error)
    or an 824 error other than a bad checksum or a torn page (such as a bad  page ID).
2 = Bad checksum.
3 = Torn page.
4 = Restored (page was restored after it was marked bad).
5 = Repaired (DBCC repaired the page).
7 = Deallocated by DBCC.

When you need to repair your database after a corruption is found then you will want to know exactly which pages were identified as having a problem. For a greater understanding about consistency checking and repairing a corrupt database you should check out these videos from Paul Randal that are a part of the MCM training materials.

With SQL2005 we were given the ability to do a page-level restore. For details on how to perform a page level restore check out this MSDN entry. You biggest takeaway from that link should be this: you cannot recover a page if your database is in the SIMPLE recovery mode (which may also explain why many people never bother looking at this table).

Where else is this information?

Nowhere. As I mentioned earlier, this was introduced with SQL 2005. In prior posts I have been displaying the names of system tables from SQL 2000 (because 40-50% of the databases my clients have running are SQL 2000), but this is something a little more modern. That’s my way of saying it was built in the 21st century (unlike SQL 2000).

OK, that last paragraph is not entirely true, as you can get details about corrupt pages form things like the DBCC output, or the event logs, or the SQL Server error log. But having them listed in a table inside of msdb makes your life a little bit easier.