Thursday, January 15, 2015

suspect pages errors

USE msdb

GO

SELECT * FROM dbo.suspect_pages


Event_type column gives different values

Errors Recorded in suspect_pages Table
The suspect_pages table contains one row per page that failed with an 824 error, up to a limit of 1,000 rows. The following table shows errors logged in the event_type column of the suspect_pages table.
Error descriptionevent_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 checksum2
Torn page3
Restored (The page was restored after it was marked bad)4
Repaired (DBCC, AlwaysOn, or mirroring repaired the page)5
Deallocated by DBCC7
The suspect_pages table also records transient errors. Sources of transient errors include an I/O error (for example, a cable was disconnected) or a page that temporarily fails a repeated checksum test.

  • How the Database Engine Updates the suspect_pages Table
    The Database Engine takes the following actions on the suspect_pages table:
    • If the table is not full, it is updated for every 824 error, to indicate that an error has occurred, and the error counter is incremented. If a page has an error after it is fixed by being repaired, restored, or deallocated, its number_of_errors count is incremented and its last_update column is updated
    • After a listed page is fixed by a restore or a repair operation, the operation updates the suspect_pages row to indicate that the page is repaired (event_type = 5) or restored (event_type = 4).
    • If a DBCC check is run, the check marks any error-free pages as repaired (event_type = 5) or deallocated (event_type = 7).

  • ---To clean up the suspect_pages table run the following

    delete s

    from msdb.dbo.suspect_pages s

    where event_type in (4,5,7)

    No comments:

    Post a Comment