A special thanks to Daniel Jones [daniel.jones.0543@gmail.com] for showing up interest and writing informative article for our blog (http://mssqlfun.com/).
Always on\Database Mirroring Automatic Page Repair
One of the most useful as well as hottest feature in SQL Server is mirroring of database. It is helpful for numerous of organizations that implements the successful as well as relatively high-availability of inexpensive strategies. However, in all the latest versions of SQL Server its service is being updated in numerous of ways such as database mirroring automatic page repair. It repairs the corrupt pages of the database. The automatic page repair is maintained by the database mirroring and Always on Availability Groups. After some specific types of errors, which had occurred in the database further make it in non-readable form. An availability replica tries to automatically repair the pages. The replica, which is unable to tread the page from another replica. If the request is implemented then, all the non-readable pages are replaced by the readable copies. It mainly resolves the occurrence of error.
Note: The automatic repair is completely differing from DBCC repair as all the database is stored by an automatic recovery of page.
Errors Occurred by Automatic Page-Repair
Database Mirroring of Automatic page repair efforts to repair in the data file through which an operation is failed as mentioned below:
- Error 883
Description: Action is executed only if the OS implement CRC (cyclic redundancy check), which fails on the data.
Instances: ERROR_CRC. The operating system value for this error is 23
- Error 824
Description: Logical
Instances: Logical data errors like checksum of torn write or bad page.
Techniques to Resolve Errors
- Primary Database
The primary database of automatic page repair is tried when the state of database is SYNCHRONIZED. Moreover, the principal is still transferring log records for the database to the secondary. The elementary sequence of actions in an automatic attempt of page-repair are as mentioned:
- When an error of read comes up on a data page in the primary database then, the primary inserts row in the suspect_pages table with the correct status of an error. For database mirroring, the principal then, requests a copy of page from the mirror. For the Always On Availability Groups, the primary broadcasts the appeal to all the secondary’s and acquires the page from the first to react. The request identifies the ID and LSN, which is presently at end of flushed log. The page is marked as pending restore. This marks it inaccessible throughout the automatic page-repair effort. Attempts to utilize this page in the course of the repair attempt will fail with an error code 829.
- After getting page request, the secondary gaps till it has recreated the log up to the LSN stated in the request. After that, the mirror efforts to utilize the page in its database copy. If the page can be used, the mirror directs the copy of page to the principal. Otherwise, the secondary proceeds an error to the principal and the automatic page-repair effort fails.
- The primary methods the response, which holds the fresh copy of page.
- After the automatic page-repair effort fixes a suspect page, the page is noticeable in the suspect_pages table as reinstated.
- Secondary Database
The input and output errors on pages, which happen on the secondary database are held in generally the similar way by mirroring and Always On Availability Groups.
- With mirroring of database, if the mirror comes upon one or more page errors of I/O when it rebuilds a log record, the session of mirroring arrives with the SUSPENDED state. Through Always On Availability Groups, if a replica of secondary meets one or more page errors of I/O when it recreates a log record, the secondary database enters the state of SUSPENDED. At that point, the mirror insets a row in the suspect_pages table with the suitable error status. The secondary then, requests a copy of page from the primary.
- The principal attempts to use the page in its database copy. If the page can be edited, the primary directs the copy of page to the secondary.
- If the mirror obtains copies of each page it has demanded, the secondary efforts to restart the mirroring session. If an automatic page-repair go fixes a suspect page, the page is clear in the suspect_pages table as reinstated.
If secondary does not obtain a page, which is requested from the principal, the automatic page-repair effort fails. With mirroring of database, the mirroring session leftovers suspended. With Always On Availability Groups, the secondary database leftovers suspended. If the mirroring session or secondary database is restarted manually, the corrupted pages will be again hit at the time of synchronization phase.
View Attempts of Automatic Page-Repair
The mentioned dynamic management previews return rows for the up-to-date automatic page-repair efforts on a given availability database, with maximum of 100 rows per database.
- Always On Availability Groups:
Gives a row for each automatic page-repair effort on any database availability on availability replica, which is hosted for any group availability by server instance.
- Database mirroring:
sys.dm_db_mirroring_auto_page_repair
Returns row for all automatic page-repair try on any database mirrored on the server instance.
Conclusion
It is important to repair the page to maintain the continuity of the work. Therefore, we have discussed Database mirroring automatic page repair in the above discussion that makes easy for users to remove the hurdle and understand it clearly.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
