Message Handling Can Affect Your Availability Group Replicas in SQL Server

The state of databases participating in Always On are prone to change due to message handling. A sudden failover might interrupt a large transaction happening in the database that participates in Always On. This will result in a lengthy undo process and database status would be ‘Not Synchronizing’.

Error message but no user action needed

Availability Group Replicas In SQL ServerIf your availability group is having hundreds of databases and heavy workload runs on the availability group, a sudden failover will change the status of databases in your availability group. This will add a lot of messages to the SQL log. Though these messages carry an error number, it is for informational purpose and no user action is required. The table below shows more info about such error message.

message_id 35278
language_id 1033
severity 16
is_event_logged 0
Message Availability database ‘%.*ls’, which is in the secondary role, is being restarted to resynchronize with the current primary database. This is an informational message only. No user action is required.

Phases in roll back

SQL AlwaysOn Availability GroupsThe rollback of a large transaction and recovery events on readable secondary replica must be complete for the synchronization to resume between Primary and Secondary replicas. You can track this transition using SQL Server error log. After a failover, at your secondary replica, scan the SQL server error log for messages like “Remote harden of transaction%”. After the roll back of the transition databases in your availability group attain the secondary role. The final state is the Reverting after which secondary will start to receive pages from the primary replica.

If you are comfortable in using T-SQL, you can use the below code to fetch replica states.

Select * from sys.dm_hadr_database_replica_states

Status check

Though the message says that no user action is necessary, when you see such errors in your SQL log, it is a good practice to ensure that availability groups are in a healthy state. You can easily do that using Availability groups dashboard. Connect to the target SQL Server instance. Expand the node AlwaysOn High Availability, right click on the node Availability Groups and click the option Show Dashboard.

Automated status check

If your SQL environment is huge, checking the status of AlwaysOn groups is a tedious task. In such cases, you can automate it using T-SQL. The result set will have the following fields:

1.       replica server name
2.       database name
3.       availability group name
4.       is local
5.       synchronization state description
6.       is commit participant
7.       synchronization health description
8.       recovery log sequence number
9.       truncation log sequence number
10.   last sent log sequence number
11.   last sent time
12.   last received log sequence number
13.   last received time
14.   last hardened log sequence number
15.   last hardened time
16.   last redone log sequence number
17.   last redone time
18.   log send queue size
19.   log send rate
20.   redo queue size
21.   redo rate
22.   filestream send rate
23.   end of log sequence number
24.   last commit log sequence number
25.   last commit time

Though SQL Server AlwaysOn allows you to have readable secondary replicas, never miss to deploy backup plans without which recovering a corrupted mdf would be very difficult.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix Outlook and excel recovery software products. For more information visit www.datanumen.com

3 Comments

  1. Comment by Smith:

    I am pleased to get
    this page
    keep doing great

  2. Comment by Akbar:

    It’s really great article on SQL server. I learn many new topics about SQL server from your article. Keep doing great!

  3. Comment by KitBibb:

    Great article for SQL server. Specially Automated status check list is very helpful for me. Because I didn’t know about this before. Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *