How to Resolve SQL Server Page Level Corruption

SQL Server reports a read error on one data page and your queries fail. Other parts of the database look fine.

This guide explains what page level corruption is and shows how to find the damaged pages and restore them from a backup. Every command below is untested and follows the official pages, so try it on a copy first.

What is SQL Server Page Level Corruption?

SQL Server stores data in 8 KB pages. Page level corruption means that the engine cannot read one or more of those pages correctly.

Microsoft states that a page becomes suspect when a read returns an 823 error caused by an operating system CRC such as a disk error or an 824 error such as a torn page. See managing the suspect pages table.

• Hardware failure. Failing disks and controllers damage pages.
• I/O path problems. Broken cables and network connections can return bad reads.
• Logical errors. Torn pages and bad checksums mark a page as damaged.

How do You Find the Damaged Pages?

SQL Server records suspect pages during a query read, a DBCC CHECKDB operation and a backup. It stores them in the suspect_pages table of the msdb database.

SELECT * FROM msdb.dbo.suspect_pages
WHERE event_type IN (1, 2, 3)

Check the whole database with DBCC CHECKDB. Microsoft states that the command validates the logical and physical integrity of all objects in the database. See DBCC CHECKDB.

DBCC CHECKDB (PagesDemoDB) WITH NO_INFOMSGS

This table stores up to 1,000 rows and logs no new errors after it fills. Delete old rows with event types 4, 5 and 7 on a schedule.

How do You Restore Damaged Pages From a Backup?

Microsoft states that a page restore replaces only the damaged pages with copies from a backup. Other pages in the database remain available. See restore pages.

1. Find the file ID and page ID of each damaged page in suspect_pages.
2. Restore the pages from a full backup with NORECOVERY.
3. Restore every later log backup with NORECOVERY.
4. Create a new log backup and restore it with RECOVERY.

RESTORE DATABASE PagesDemoDB PAGE = '1:57, 1:202'
FROM DISK = N'D:\Backups\PagesDemoDB_Full.bak'
WITH NORECOVERY;
RESTORE LOG PagesDemoDB FROM DISK = N'D:\Backups\PagesDemoDB_Log1.trn'
WITH NORECOVERY;
BACKUP LOG PagesDemoDB TO DISK = N'D:\Backups\PagesDemoDB_Tail.trn';
RESTORE LOG PagesDemoDB FROM DISK = N'D:\Backups\PagesDemoDB_Tail.trn'
WITH RECOVERY;

What can Page Restore Not Fix?

Microsoft states that page restore cannot restore the transaction log, the allocation pages (GAM, SGAM and PFS), page 0 of every data file, page 1:9 or a full text catalog.

Microsoft also states that restoring a whole file is more efficient when you must restore more than a few pages in one file.

Should You Use REPAIR_ALLOW_DATA_LOSS?

No unless no backup exists. Microsoft states that the option can cause more data loss than a restore from the last known good backup and calls it an emergency last resort.

Microsoft also advises you to create physical copies of the database files before you use it. Deallocated data cannot be recovered afterward.

How do You Prevent Page Corruption?

• Back up on a schedule. Page restore needs a full backup and the log chain.
• Check on a schedule. Microsoft recommends DBCC CHECKDB with PHYSICAL_ONLY for frequent use and a full check at longer intervals.
• Watch the disk. Many new suspect page rows point to a problem with the I/O subsystem.
• Use availability groups. Replicas can repair corrupted pages from another replica automatically.

For the larger picture see the SQL Server disaster recovery options. For the Microsoft page on automatic page repair see availability groups overview.