MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter























Trending SQL Server Tips














How to Detect Database Corruption Issues in SQL Server Using Suspect_Pages Table of MSDB Database?

Read Comments   |   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:

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
Bad checksum 2
Torn page 3
Restored (The page was restored after it was marked bad) 4
Repaired (DBCC repaired the page) 5
Deallocated by DBCC 7

FAQ: 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"

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.

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
GO

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)
GO

Automatic Updates to SUSPECT_PAGES table

Any of the below actions will automatically delete rows from SUSPECT_PAGES table when you execute:-

  • DROP DATABASE which has suspect pages
  • When execute ALTER DATABASE REMOVE FILE command
  • When you execute DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS command as this updates the SUSPECT_PAGES table to indicate each page that it has deallocated or repaired.
  • Database RESTORE command also updates the SUSPECT_PAGES table. A full, file, or page restore marks the page entries as restored.

References



Learn More...





Last Updated On: Sept 27, 2015



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: