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!