suspect_pages

When SQL Server detects corruption in a database, it reports that corruption immediately to the client who requested the data. But did you know SQL Server also stores the details about which pages have experienced corruption in the msdb database, in the suspect_pages table?

Suspect Pages, or simply Children of the Marquis de Béthune Playing with a Dog - François Hubert Drouais

Suspect Pages, or simply Children of the Marquis de Béthune Playing with a Dog? By François Hubert Drouais

Let’s take a look at the table with a simple SELECT statement:

My test instance has a single page with corruption recorded, due to some prior testing I did:

╔═════════════╦═════════╦═════════╦════════════╦═════════════╦═════════════════════════╗
║ database_id ║ file_id ║ page_id ║ event_type ║ error_count ║    last_update_date     ║
╠═════════════╬═════════╬═════════╬════════════╬═════════════╬═════════════════════════╣
║          15 ║       1 ║     328 ║          2 ║           1 ║ 2019-07-23 12:06:23.773 ║
╚═════════════╩═════════╩═════════╩════════════╩═════════════╩═════════════════════════╝

According to the Microsoft Documentation, the event_type column has the following possible values:

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.

To make the output a little more friendly, I wrote the following query:

Running the query shows the following output:

╔═══════════════╦══════════════════════╦══════╦═══════════════╦═══════╦═════════════════════════╗
║   Database    ║         File         ║ Page ║  Description  ║ Count ║       Last Update       ║
╠═══════════════╬══════════════════════╬══════╬═══════════════╬═══════╬═════════════════════════╣
║ bit_flip_test ║ bit_flip_test_system ║  328 ║ Bad checksum. ║     1 ║ 2019-07-23 12:06:23.773 ║
╚═══════════════╩══════════════════════╩══════╩═══════════════╩═══════╩═════════════════════════╝

Interestingly, the documentation says the dbo.suspect_pages table has a maximum capacity of 1,000 rows. Once the table is full, no further details about corrupt pages will be added to the table. In order to not hit that limit, it’s advisable to delete rows from the table for pages that are no longer corrupt. Use a standard DELETE FROM statement to remove old unneeded rows from the table:

Anyone with sysadmin privileges can remove rows from the suspect_pages table.

Check out the rest of our posts on SQL Server Maintenance and Internals.