Automatic Page Repair is one feature which is not really famous, however pretty much known to most of us.
I wanted to write about Automatic Page Repair since a very long time. Today I decided to test this feature when I had to deploy and do some test cases with DBM.
In simple words Auto Page Repair feature will replace the corrupt page by requesting a readable page from the partner mirrored database.
We also need to take into consideration that not all pages can be repaired. Read more about this feature here.
Let’s now do some corruptions and see if this feature is smart enough to repair them!
Note – Don’t try this at home (Production Environment) !
Stage 1
For the purpose of the demo we have a Mirroring Setup for the database AdventureWorks2012 (Downloaded from codeplex)
For the purpose of corrupting the page we will need to make the database offline first, hence will need to remove mirroring for a while as the database which is taking part in a mirroring session cannot be taken offline. We will re-establish DBM once corruption is completed.
We will pick one table from the database and will corrupt the index page of the same. The table which we are going to choose here is HumanResources.Employee
We will now pick one Index Page for the table
The page which we are going to corrupt is Page with ID 875
Note – I have noted to take required backups to ensure that proper rollback is possible.
Using HexEdit tool we can enforce corruptions for page ID 875,and this action requires the database to be offline.
Once the corruptions are made(Ref to this post to understand how can we corrupt a page) we can bring the database Online and identify the corruptions using DBCC CHECKDB command
DBCC CHECKDB(AdventureWorks2012) WITH NO_INFOMSGS
Msg 8980, Level 16, State 1, Line 1 Table error: Object ID 1237579447, index ID 1, partition ID 72057594045136896, alloc unit ID 72057594050838528 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:875) and previous child (0:0), but they were not encountered.
The above mentioned error message is taken from the DBCC result set.
Stage 2
Now that we have a corrupted page, we will proceed and re-establish mirroring.
After mirroring is re-established, we can try running DBCC CHECKDB once again on the same corrupted database.
DBCC CHECKDB(AdventureWorks2012) WITH NO_INFOMSGS
This time we will get a magical confirmation as below
Command(s) completed successfully.
Wow!!! Where did that corruption gone?
Yes, you guessed it right. Auto Page Repair is so smart that it replaced a clean page to the Principal Database from the Mirroring Partner.
No restore or what so ever, everything happened behind the scenes. Very neat, very smart !
Principal and Mirrored database is in sync and they help each other’s too.
We have a view called [sys].[dm_db_mirroring_auto_page_repair] which keeps track of all repair attempts which done behind the scenes.
Let’s quickly query it and see what’s in there
SELECT * FROM [sys].[dm_db_mirroring_auto_page_repair]
Bingo!!! The result came as
The result is very clear. It says that page 875 was corrupted and it was replaced/repaired.The action was successful and the page is reusable
BOL talks about the below page_status possible values –
2 = Queued for request from partner.
3 = Request sent to partner.
4 = Queued for automatic page repair (response received from partner).
5 = Automatic page repair succeeded and the page should be usable.
6 = Irreparable. This indicates that an error occurred during page-repair attempt, for example, because the page is also corrupted on the partner, the partner is disconnected, or a network problem occurred. This state is not terminal; if corruption is encountered again on the page, the page will be requested again from the partner.
In SQL 2012 we have an additional view for Always ON AG’s and it is
[sys].[dm_hadr_auto_page_repair]
Conclusion
In case you have a mirroring setup,then its worth querying the view and see how much the Auto Page Repair feature have helped you.
Thanks for reading.
SQLSailor 