Freigeben über

Secondary Replica may go into suspended state if you alter the readability property of the Availability Group and page compression is enabled

  • Artikel

Please apply one of the fixes mentioned at the bottom of this blog page or employ one of the suggested workarounds if you are AlwaysOn Availability Groups and have enabled page compression on any table that is part of a database in the Availability group. In such a setting, if you alter the readability property of the secondary replica (example: from Read-intent or Yes to No) the secondary replica may go into a suspended state. Additionally, you will see following messages in the error log of the secondary replica:

2015-01-06 10:40:12.10 spid72s     **Dump thread - spid = 0, EC =
0x0000004978DD4B90
2015-01-06 10:40:12.10 spid72s     ***Stack Dump being
sent to C:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
2015-01-06 10:40:12.10
spid72s     *
*******************************************************************************
2015-01-06
10:40:12.10 spid72s     *
2015-01-06 10:40:12.10 spid72s     * BEGIN STACK
DUMP:
2015-01-06 10:40:12.10 spid72s     *   01/06/15 10:40:12 spid
72
2015-01-06 10:40:12.10 spid72s     *
2015-01-06 10:40:12.10 spid72s    
* Location:  page.cpp:3898
2015-01-06 10:40:12.10 spid72s     * Expression: 
!pageFull
2015-01-06 10:40:12.10 spid72s     * SPID:   72
2015-01-06
10:40:12.10 spid72s     * Process ID:  2876
2015-01-06 10:40:12.10
spid72s     *
2015-01-06 10:40:12.10 spid72s     * 
2015-01-06
10:40:12.10 spid72s     *
2015-01-06 10:40:12.10 spid72s     * 
MODULE                          BASE      END       SIZE
2015-01-06
10:40:12.10 spid72s     * sqlservr                       00007FF6899A0000 
00007FF6899F9FFF  0005a000
2015-01-06 10:40:12.10 spid72s     *
ntdll                          00007FFDC9AE0000  00007FFDC9C89FFF 
001aa000
...

2015-01-06 10:40:16.33 spid72s     Stack Signature for the dump is 0x0000000014098401
2015-01-06 10:40:17.19 spid72s     External dump process return code 0x20000001.
External dump process returned no errors.

2015-01-06 10:40:17.19 spid72s     Error: 17066, Severity: 16, State: 1.
2015-01-06 10:40:17.19 spid72s     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.
2015-01-06 10:40:17.20 spid72s     Error: 3624, Severity: 20, State: 1.
2015-01-06 10:40:17.20 spid72s     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.
2015-01-06 10:40:35.40 spid72s     AlwaysOn Availability
Groups data movement for database 'MyDatabase' 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.
2015-01-06 10:40:35.40 spid72s     Error: 3313, Severity: 21, State: 2.
2015-01-06 10:40:35.40 spid72s     During redoing of a logged operation in database 'MyDatabase', 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.

WORKAROUND:

You can use one of the following workarounds to prevent this issue till such time you can apply the fix.

  1. Do not change the readability property of the availability group.
  2. Turn off page compression.

FIX:

For SQL Server 2012, apply the fix as per KB Article:

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

https://support.microsoft.com/kb/3054530/EN-US

For SQL Server 2014, apply the following fix:

Microsoft® SQL Server® 2014 SP1

Comments

  • Anonymous
    October 11, 2015
    Bom trabalho