Home / Databases / SQL Server

DatabasesTroubleshootingAdvanced

Always On availability group database not synchronizing: diagnosis and recovery

Check replica and database state with DMVs, interpret send and redo queues, and resume or reseed a database that is NOT SYNCHRONIZING or SUSPENDED.

Deepak Bhardwaj2 min read

Applies toSQL Server 2016–2022 Always On availability groups (Windows and Linux)

1. Overall state

SELECT ag.name AS ag, ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
       ars.synchronization_health_desc, ars.last_connect_error_description
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ars.group_id = ag.group_id;

SELECT DB_NAME(database_id) AS db, ar.replica_server_name, drs.synchronization_state_desc,
       drs.is_suspended, drs.suspend_reason_desc,
       drs.log_send_queue_size AS send_queue_kb, drs.redo_queue_size AS redo_queue_kb,
       drs.last_commit_time
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id;

2. Read the result

Observation Meaning Next step
connected_state_desc = DISCONNECTED Replicas can't talk Endpoint, firewall (TCP 5022), certificates/service account
is_suspended = 1 Data movement suspended (manually or due to an error) See suspend_reason_desc, check the error log, then resume
Large send queue Primary can't ship log fast enough Network bandwidth/latency
Large redo queue Secondary can't apply log fast enough Secondary disk IO, CPU, or blocked redo by readable queries
NOT SYNCHRONIZING with an error 35285/35278 in the log The database can't join or recover Possibly reseed

3. Endpoint and connectivity

SELECT name, state_desc, port FROM sys.tcp_endpoints WHERE type_desc = 'DATABASE_MIRRORING';

Test-NetConnection sqlnode2 -Port 5022

The endpoint must be STARTED, and the SQL Server service account of each replica needs CONNECT on the other's endpoint.

4. Resume data movement

-- on the secondary
ALTER DATABASE Sales SET HADR RESUME;

If it suspends again immediately, read the SQL Server error log on the secondary. A common reason is a full disk on the secondary (the log can't grow there).

5. Redo blocked by readable secondary queries

SELECT session_id, command, blocking_session_id, wait_type
FROM sys.dm_exec_requests WHERE command = 'DB STARTUP';

A reporting query holding a schema lock can block redo. Kill the blocker, or move heavy reporting off that replica.

6. Reseed (last resort)

When the secondary's copy is too far behind or damaged:

  1. Remove the database from the AG on the secondary: ALTER DATABASE Sales SET HADR OFF;
  2. Restore the latest full and log backups WITH NORECOVERY, or use automatic seeding:
ALTER AVAILABILITY GROUP AG1 MODIFY REPLICA ON 'SQLNODE2' WITH (SEEDING_MODE = AUTOMATIC);
-- on the secondary
ALTER AVAILABILITY GROUP AG1 GRANT CREATE ANY DATABASE;
  1. Join it: ALTER DATABASE Sales SET HADR AVAILABILITY GROUP = AG1;

Warning

While a synchronous secondary isn't synchronized, automatic failover to it is unavailable. Tell the customer the HA protection is reduced until it's healthy again.

Related fixes