Friday, July 25, 2008

Did you check your MSDB.dbo.suspect_pages after a database restore?

In SQL Server 2005, the default behavior of a RESTORE command is to simply continue even if there are corrupted pages in your backups. The only way to find out if there are corruptions is when a user gives you a call saying that they could not query some records and probably gets an error similar to the one below

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error:
incorrect checksum (expected: 0xf93a020c; actual: 0xf93a820c).
It occurred during a read of page (1:69) in database ID 10 at offset 0x0000000013c000 in file
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SalesDB.mdf'.
Additional messages in the SQL Server error log or system event log may provide more detail.
This is a severe error condition that threatens database integrity and must be corrected immediately.


The first thing you need to do after a database restore is to check the MSDB.dbo.suspect_pages to see if there are suspect pages that need to be repaired. Books Online describes the structure of this system table and gives you an idea on what to restore should there be suspect pages. Bear in mind though that this system table can only hold up to a maximum of 1000 records and everything get cycled out so you need to do your own housekeeping.

The Enterprise Edition gives you an option to do a page-level restore should you have corrupt databases. A more low-level discussion on fixing damaged pages can be found on the SQL Server Storage Engine blog

No comments:

Google