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? By François Hubert Drouais
Let’s take a look at the table with a simple SELECT statement:
|
1 2 |
SELECT * FROM msdb.dbo.suspect_pages; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
USE msdb; IF OBJECT_ID(N'tempdb..#suspect_page_events', N'U') IS NOT NULL BEGIN DROP TABLE #suspect_page_events; END CREATE TABLE #suspect_page_events ( event_type int NOT NULL PRIMARY KEY CLUSTERED , event_description varchar(144) NOT NULL ); INSERT INTO #suspect_page_events (event_type, event_description) 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.'); SELECT [Database] = d.name , [File] = mf.name , [Page] = sp.page_id , [Description] = spe.event_description , [Count] = sp.error_count , [Last Update] = sp.last_update_date FROM dbo.suspect_pages sp INNER JOIN sys.databases d ON sp.database_id = d.database_id INNER JOIN sys.master_files mf ON sp.database_id = mf.database_id AND sp.file_id = mf.file_id LEFT JOIN #suspect_page_events spe ON sp.event_type = spe.event_type ORDER BY sp.last_update_date DESC; |
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:
|
1 2 3 |
DELETE sp FROM msdb.dbo.suspect_pages sp WHERE sp.last_update_date <= DATEADD(DAY, -180, GETDATE()); |
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.
[…] Hannah Vernon explains what the suspect_pages table is in msdb: […]