SQL Server mirroring – page level repair

Data in SQL server are stored in blocks of 8kb, called pages, and if a page is corrupted the data may be lost. You can find corrupted pages by running DBCC CHECKDB or when you query the table containing the corrupt page. If you encounter a corrupt page, you have some options:

• DBCC CHECKDB(‘yourDBName’,REPAIR_ALLOW_DATA_LOSS | REPAIR_REBUILD), but DBCC CHECKDB reports the minimal repair mode and you shouldn’t spend a lot of time trying to repair the database if you’ll loose data
• Rebuild index if the corrupt page is part of a non-clustered index
• Restore the database,filegroup or even page

But if you are using mirroring in SQL server 2008 and later you have features for automatic page repair. The feature is based on the fact that the principal and mirror databases are exactly the same. So, when a page becomes corrupt on the principal, the page from the mirror is used to fix the principal and if a page becomes corrupt on the mirror, the page is read from the principal to repair the mirror.

It sounds like magic, but it’s a really cool feature and I’ve seen it live in a client environment. It actually works! If you encounter page corruption when you use mirroring and then runs DBCC CHECKDB to investigate the corruption you’ll probably not find the corruption as it’s already repaired.

Mirroring is easy to configure and is availiable in standard edition and above.

Page corruption and page repairs are logged in the SQL server log.

2013-09-27 10:23:19.96 spid34s     Database mirroring is attempting to repair physical page (x:yyyy) in database “databasename” by requesting a copy from the partner.
2013-09-27 10:24:20.42 spid34s     Database mirroring successfully repaired physical page (x:yyyy) in database “databasename” by obtaining a copy from the partner

You can also use the sys.suspect_pages table in MSDB to see any corruption and page restores (read more at http://technet.microsoft.com/en-us/library/ms191301.aspx ):

SELECT * FROM msdb.sys.suspect_pages

Event type 4 and 5 are restored or repaired pages.

Don’t forget that corruption is often a result of hardware failure and you should investigate your storage solution to see if you have hardrives with bad sectors.

Feel free to contact us if you want to know more about SQL Server mirroring.

Håkan Winther
Twitter: @h_winther