FIX: Corruption occurs on pages of secondary replica when you change the secondary replica to unreadable

Applies to: SQL Server 2012 DeveloperSQL Server 2012 EnterpriseSQL Server 2012 Standard

Symptoms


Assume that you use the AlwaysOn Availability Groups feature in Microsoft SQL Server 2012. When you change the connection access of the secondary replica from "readable" to "unreadable," a corruption occurs on pages that use page compression in the given replica.

Availability databases that experience this problem at the secondary replica cannot recover because of an error during the redo phase of synchronization. The secondary replica will not synchronize with the primary replica, and it reports a synchronization state of "SUSPEND_FROM_REDO." Additionally, you receive the following error messages in the error log of SQL Server that hosts the secondary replica:
<Date> <Time> spid<ID>     Error: 17066, Severity: 16, State: 1.
<Date> <Time> spid<ID>     SQL Server Assertion: File: <page.cpp>, line=3898 Failed Assertion = '!pageFull'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
<Date> <Time> spid<ID>     Error: 3624, Severity: 20, State: 1.
<Date> <Time> spid<ID>     A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
<Date> <Time> spid<ID>     AlwaysOn Availability Groups data movement for database '<DataBase Name>' has been suspended for the following reason: "system" (Source ID 2; Source string: 'SUSPEND_FROM_REDO'). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.
<Date> <Time> spid<ID>     Error: 3313, Severity: 21, State: 2.
<Date> <Time> spid<ID>     During redoing of a logged operation in database '<DataBase Name>', an error occurred at log record ID (1786:4978584:74). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
<Date> <Time> spid<ID>      ALTER DB param option: RESUME
<Date> <Time> spid<ID>      AlwaysOn Availability Groups data movement for database '<DataBase Name>' has been resumed. This is an informational message only. No user action is required.
<Date> <Time> spid<ID>     Nonqualified transactions are being rolled back in database <DataBase Name> for an AlwaysOn Availability Groups state change. Estimated rollback completion: 100%. This is an informational message only. No user action is required.
<Date> <Time> spid<ID>     AlwaysOn Availability Groups connection with primary database terminated for secondary database '<DataBase Name>' on the availability replica with Replica ID: {bbdedecb-f26b-47e9-9e7d-7c22f99edb23}. This is an informational message only. No user action is required.
<Date> <Time> spid<ID>     Starting up database '<DataBase Name>'.
<Date> <Time> spid<ID>     Recovery of database '<DataBase Name>' (13) is 0% complete (approximately 781 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
……

Resolution


The issue was first fixed in the following cumulative update of SQL Server.

Cumulative Update 6 for SQL Server 2012 SP2

Cumulative Update 16 for SQL Server 2012 SP1

More Information


The previous issue may occur when the read access is changed for the secondary replica.
You can set the read access of availability databases on the secondary replica by using the following two methods:
  • Set the read access by using the ALTER AVAILABILITY GROUP command:
    ALTER AVAILABILITY GROUP [AGName]
    MODIFY REPLICA ON N'<SRV>' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))
  • Set the read access by changing the settings in Object Explorer of SQL Server Management Studio (SSMS):
    • Connect to the server, and then open the AlwaysOn Availability folder.
    • Open the Availability Groups folder.
    • Right-click the availability group, and select Properties.
    • Change the Readable Secondary property for the secondary replica to No, and then click Ok.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.