Dear Friends,
SQL Server Automatic page repair is one of the cool features of database mirroring which helps us to replace the corrupt page by requesting a readable copy from the partner database. Today, I’ll simulate a page corruption and show how this feature is helpful to recover damaged page.
For this demonstration, I’ve used database mirroring setup in my LAB environment on AdventureWorks2012 database.
In order to corrupting the page, I’ll have to offline my database hence I’ll have to remove database mirroring as mirrored database cannot be offline. Once I’m done with corrupting the page mirroring can be re-established. I picked up HumanResources.JobCandidate table and decided to corrupt Index Page 1057. I went ahead and corrupted the page. You can read my earlier blog post here to know how we can corrupt a page definitely in a controlled LAB environment. Time to run CHECKDB;
DBCC CHECKDB(AdventureWorks2012) WITH NO_INFOMSGS
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1589580701, index ID 1, partition ID 72057594045399040, alloc unit ID 72057594051100672 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:1057) and previous child (0:0), but they were not encountered.
So, I corrupted page 1057 and now we can re-instate database mirroring and check how database looks post corruption. I went ahead and reestablished database mirroring then ran DBCC CHECKDB;
Yes, you guessed it right corrupted page is automatically replaced from partner so CHECKDB reported clean. There is a DMV called sys.dm_db_mirroring_auto_page_repair so let’s query this and find out how it looks;
As can be seen that error_type shown 2 and page_status is 5 i.e. there was a bad checksum on this database and automatic page repair succeeded. To know more on sys.dm_db_mirroring_auto_page_repair you can read here. I had a check on error log and it also recorded useful information as shown below;
To give you more details, on SQL Server Enterprise and Developer editions database mirroring can automatically correct 823, 824 error caused by data cyclic redundancy check errors when the server is attempting to read a page. When mirroring partner cannot read a page, it asynchronously requests a copy from the partner; if the requested page is successfully applied the page repair is reported as successful. Actual data is preserved during restore however it does not repair allocation pages and control type pages. Repair operation varies if principal or mirror is requesting the page and discussed following;
When Principal Request a Page
When principal identifies a page read error, it marks the page with an 829 error i.e. RestorePending and inserts a row into the suspect_pages table in MSDB with the error status then requests the page from the partner. If the mirror is successful in reading the page it returns the page to the principal who then applies it. After the page is repaired the principal marks the page as restored i.e. event_type =5 in the suspect_pages table. Then any deferred transactions associated are resolved accordingly.
When Mirror Request a Page
When mirror identifies a page read error, marks the page with an 829 error i.e. RestorePending and inserts a row into the suspect_pages table in MSDB with the error status information. It requests the page from the principal and sets the mirror session in SUSPENDED state. If the principal is successful in reading the page it returns the page to the mirror. Once the pages applied at the mirror the mirror resumes the data mirroring session and marks the page as restored in the suspect_pages table with event_type = 5.
You can refer to my earlier blog post here to know more about RestorePending.
Please note not all pages can be repaired and you can read here to know further.
Regards
Kanchan Bhattacharyya
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
