SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Why Availability Databases Are Not Synchronizing?

If you are working on SQL Server Availability Group Databases, you may have seen that Availability Group is online but at-least one availability database at the primary or the secondary is not synchronizing.

In the blog, we are going to explore how to find what has interrupted synchronization between availability databases on the primary and a secondary replica.

Possible causes for not synchronizing:

  • The database administrator manually suspended synchronization for the availability database(s) using:

ALTER DATABASE <dbname> SET HADR SUSPEND

  • The system suspended synchronization following fail-over event.
  • The system suspended synchronization due to an integrity or recovery issue with the underlying database.
  • Other causes.

To find the reason for the database suspension, run the below query on the local SQL Server in which the database is suspended;

SELECT 
DB_NAME(database_id) AS databasename, 
synchronization_state_desc, 
synchronization_health_desc, 
suspend_reason, 
suspend_reason_desc 
FROM sys.dm_hadr_database_replica_states
WHERE is_local=1

For example:- let’s say a database administrator has suspended the “Data Movement” to a secondary for maintenance purpose but never resumed synchronization. Now, you open the AlwaysOn dashboard and realizes that his availability group containing that availability database is not healthy. You can query to find the reason.

 Happy Learning!

The post Why Availability Databases Are Not Synchronizing? appeared first on .

SQL Geek

Dharmendra is a SQL Server/Microsoft Data Platform professional with over eight years of experience. He enjoys helping others in the SQL Server community and does this by contributing on blogs, speaking at several SQL events. His passion and focus is to explore and share more and more on SQL Server.

Comments

Leave a comment on the original post [www.dharmendrakeshari.com, opens in a new window]

Loading comments...