Understanding and Managing the suspect_pages Table

SQL Server 2005 maintains information about suspect pages in the suspect_pages table in the msdb database of each server instance. The suspect_pages table is relevant to deciding whether a restore is necessary.

When the SQL Server Database Engine reads a database page that contains an 824 error (see the following table), the page is considered "suspect," and its page ID is recorded in the suspect_pages table. The Database Engine records any suspect pages encountered during regular processing, such as the following:

• A query has to read a page.
• During a DBCC CHECKDB operation.
• During a backup operation.

The suspect_pages table is also updated as necessary during a restore operation, a DBCC repair operation, or a drop database operation.

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 824 errors are logged in the event_type column of the suspect_pages table.

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

Automatic Updates to the suspect_pages Table

The following actions automatically delete rows from the suspect_pages table:

• ALTER DATABASE REMOVE FILE
• DROP DATABASE
• DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS updates the suspect_pages table to indicate each page that it has deallocated or repaired.
• RESTORE also updates the list. A full, file, or page restore marks the page entries as restored.

Maintenance Role of the Database Administrator

Database administrators are responsible for managing the table, primarily by deleting old rows. The suspect_pages table is limited in size, and if it fills, new errors are not logged. When this table approaches its limit, the database administrator or system administrator must manually clear out old entries from this table by deleting rows. No more entries are allowed until a database administrator has done this.

A database administrator can also insert or update records. For example, updating a row might useful when the database administrator knows that a particular suspect page is actually intact, but wants to preserve the record for a while.

Examples

The following example deletes some of the rows from the suspect_pages table.

--  Select restored, repaired, or deallocated pages.
DELETE FROM msdb..suspect_pages
   WHERE (event_type = 4 OR event_type = 5 OR event_type = 7);
GO

The following example selects on the bad pages in the suspect_pages table.

-- Select nonspecific 824, bad checksum, and torn page errors.
SELECT * FROM msdb..suspect_pages
   WHERE (event_type = 1 OR event_type = 2 OR event_type = 3);
GO

See Also

Concepts

Performing Page Restores

Other Resources

DROP DATABASE (Transact-SQL)
RESTORE (Transact-SQL)
BACKUP (Transact-SQL)
DBCC (Transact-SQL)
suspect_pages (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance