In today’s article, we will talk about how to detect SQL Server Suspect Pages.
I will explain how you can detect database corruption using the suspect_pages table in the msdb database.
SQL Server announced in 2005 that the SUSPECT_PAGES table is available in the msdb system database. Since SQL Server 2005, every corrupted page information is added to the msdb.dbo.suspect_pages table.
Due to SQL Server database structure, if it encounters one of the following errors, it is considered corrupted. (According to the information on MSDN)
Error 823 occurs when a CRC (Cyclic Redundancy Check) error occurs by the operating system, such as disk failure or some hardware failure. A Windows read or write job has failed.
Torn Page Logical Consistency – 824 error – This error occurs because windows is reading the page successfully but the sql server is experiencing an inconsistency on the page. It has an infrastructure similar to the 823 error.
When will a record arrive in the SUSPECT_PAGES table?
The SQL Server database engine provides logging of page corruptions encountered during any of the following operations.
During the execution of the DBCC CHECKDB command
During Database Backup and Restore
During a corrupted page read of the query being run
During DBCC CHECKDB REPAIR_ALLAW_DATA_LOSS operation
While deleting database – Drop Database operations
Important note! :
We should run the DBCC CHECKDB command periodically during maintenance.
What are the errors recorded in the SUSPECT_PAGES table?
The following table contains the descriptions of the errors according to the event_type data.
| Event_type | Error Description |
| 1 | Error records 823 and 824 at the operating system level – CRC Error |
| 2 | Bad CheckSum |
| 3 | Torn Page |
| 4 | Restored (The page was restored after it was marked bad) –The page was broken, but it has been repaired. |
| 5 | Repaired with DBCC |
| 7 | Deallocated by DBCC |
We can find corrupted pages using the following query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT SD.name AS DatabaseName ,MSP.file_id AS FileID ,SMF.physical_name AS PhysicalFilePath ,MSP.page_id AS PageID ,CASE WHEN MSP.event_type = 1 THEN ‘823 error caused by an operating system CRC error or 824 error other than a bad checksum or a torn page’ WHEN MSP.event_type = 2 THEN ‘Bad checksum’ WHEN MSP.event_type = 3 THEN ‘Torn Page’ WHEN MSP.event_type = 4 THEN ‘Restored (The page was restored after it was marked bad)’ WHEN MSP.event_type = 5 THEN ‘Repaired (DBCC repaired the page)’ WHEN MSP.event_type = 7 THEN ‘Deallocated by DBCC’ END AS EventDescription ,MSP.error_count AS ErrorCount ,MSP.last_update_date AS LastUpdated FROM msdb.dbo.suspect_pages MSP INNER JOIN sys.databases SD ON SD.database_id = MSP.database_id INNER JOIN sys.master_files SMF ON SMF.database_id = MSP.database_id AND SMF.file_id = MSP.file_id |
Maintaining the SUSPECT_PAGES table
Database administrators should periodically review the SUSPECT_PAGES table and delete old rows as this table is limited to 1000 rows.
If the table is full, then new errors will not be recorded.
In order to delete data from the SUSPECT_PAGES Table, you must be logged into SQL Server with a user with db_owner authority or sysadmin authority from the msdb database.
|
1 2 3 4 |
DELETE FROM msdb..suspect_pages WHERE (event_type = 4 OR event_type = 5 OR event_type = 7) |
