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
If 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||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
The 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
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.
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