How to Detect Database Corruption Issues in SQL Server Using Suspect_Pages Table of MSDB Database?
Related Topics: SQL Server Database Corruption>
Next Topic: How to Repair Suspect Database
How to Monitor Suspect Pages in SQL Server?
Most important job responsibility of a Database Administrator is to be proactive enough to detect database corruption issues within SQL Server Database. For any organization, DATA is very critical and any kind of corruption is least acceptable. Let us discuss in this article how a DBA can use SUSPECT_PAGES table available in MSDB system database to keep track of corrupt pages in any of the SQL Server Database residing on an instance of SQL Server.
Note: In SQL Server 2005, Microsoft had introduced SUSPECT_PAGES table in MSDB system database. At the same time there is no substitute for Reliable Database Backups.
Suspect Pages Table in MSDB
Starting SQL Server 2005 the page ID of every suspect page is recorded within the SUSPECT_PAGES (Suspect Page) table of MSDB system database. SQL Server Database Engine considers a page as SUSPECT when it encounters one of the following errors while the database engine tries to read a data page.
As per MSDN:-
- An 823 error that is caused by a cyclic redundancy check (CRC) issued by the operating system, such as a disk error or certain hardware errors. A Windows read or write request has failed. The error code that is returned by Windows and the corresponding text are inserted into the message. In the read case, SQL Server will have already retried the read request four times. This error is often the result of a hardware error, but may be caused by the device driver.
- An 824 error, such as a torn page or any logical consistency-based I/O error. This error indicates that Windows reports that the page is successfully read from disk, but SQL Server has discovered something wrong with the page. This error is similar to error 823 except that Windows did not detect the error. This usually indicates a problem in the I/O subsystem, such as a failing disk drive, disk firmware problems, faulty device driver
Recommendation: It is highly recommended to check for additional messages in the SQL Server Error Log or system event log If you encounter any such errors.
When SUSPECT_PAGES table of MSDB database is populated?
SQL Server Database Engine records any suspect pages encountered during any of the below mention operations such as:
- During the execution of DBCC CHECKDB Command
- During the database backup operation
- Time when a query read a page which is suspect
- During the time DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS operation is performed
- When a database is dropped
- During the database restoration operation
Best Practice: As a best practice DBA must run DBCC CHECKDB command during maintenance at regular intervals. For more information, see, When Last Time DBCC CHECKDB ran successfully on all the database in an SQL Server Instance.
Errors which are Recorded in SUSPECT_PAGES Table in MSDB Database
The SUSPECT_PAGES table contains one row per page which has failed with an 824 error, up to a limit of 1,000 rows. The following table describes errors which are logged in the EVENT_TYPE column of the SUSPECT_PAGES table.
|Error Description||event_type value|
|823 error caused by an operating system CRC error or 824 error other than a bad checksum or a torn page (for example, a bad page ID)||1|
|Restored (The page was restored after it was marked bad)||4|
|Repaired (DBCC repaired the page)||5|
|Deallocated by DBCC||7|
Resources: Learn more about How to Fix Database Corruption Issues in SQL Server
Query to Find Corrupt Database Pages on a SQL Server Instance
Execute the below mentioned TSQL Query on your SQL Server Instance to find the list of pages where corruption has occurred.
SD.NAME AS DatabaseName
,MSP.file_id AS FileID
,SMF.physical_name AS PhysicalFilePath
,MSP.page_id AS PageID
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
Maintenance of SUSPECT_PAGES Table in MSDB Database
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 if any will not be logged. In order to insert, update or delete rows you must be a member of db_owner fixed database role on msdb or the sysadmin fixed server role.
Deletes Restored, Repaired, or DeAllocated pages
Refer table for event_typevalue
DELETE FROM msdb..suspect_pages
WHERE (event_type = 4 OR event_type = 5 OR event_type = 7)
Automatic Updates to SUSPECT_PAGES table
Any of the below actions will automatically delete rows from SUSPECT_PAGES table when you execute:-
Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Built your Career
Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server
Database Corruption: How to Repair Suspect Database in SQL Server
SQL Server Database Backup Tutorial for DBAs and Developers
How to Enable BACKUP CHECKSUM
Feature in SQL Server for all Database Using Trace Flag
When Last Time DBCC CHECKDB ran successfully on all the database in an SQL Server Instance
Why SQL Server Log backups fails and Full backup succeeds? Learn how to Fix "BACKUP detected corruption in the database log error in SQL Server"
Last Updated On: Sept 27, 2015
Please leave your Valuable Comment or Let us know how this article helped you: