Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

Availability group database 'NOT SYNCHRONIZING': read the suspend reason, then resume

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

A database in an Always On availability group shows NOT SYNCHRONIZING when data movement is suspended, when the secondary cannot reach the primary, or while the database passes through a start-up or role-change transition. The cause is readable from the replica's own state: is_suspended, suspend_reason_desc and the connection state. Find the reason first, because a resume that is issued before the cause is fixed can fail or suspend again.

What the error means

Microsoft defines synchronization_state 0, NOT SYNCHRONIZING, in sys.dm_hadr_database_replica_states. For a primary database it means the database is not ready to synchronise its transaction log with the secondaries. For a secondary database it means the database has not started log synchronisation because of a connection issue, is being suspended, or is going through transition states during start-up or a role switch.

The same view shows synchronization_health: 0 is not healthy, and it applies when the state is NOT SYNCHRONIZING. Microsoft warns that the values have only local relevance. On the primary, the columns for a given secondary reflect only what the primary currently knows about it, so query both replicas before drawing a conclusion.

Suspension has two forms. Suspending a secondary database stops that secondary only, and the primary stays available. Suspending the primary database stops data movement to every secondary. Microsoft notes that while a secondary database is suspended, the send queue of the primary accumulates unsent log records and the primary's transaction log cannot be truncated. That is why a long suspension is an outage risk, not just a status light.

Likely causes

Cause What you see How to confirm
A person suspended data movement is_suspended = 1, suspend_reason_desc = SUSPEND_FROM_USER The DMV on the secondary replica, plus change records for the window
Forced failover SUSPEND_FROM_PARTNER ("suspended after a forced failover") Recent failover history and the SQL Server error log
An error while applying or redoing log SUSPEND_FROM_REDO, SUSPEND_FROM_APPLY, SUSPEND_FROM_UNDO The SQL Server error log on the secondary; Microsoft points to the error log for APPLY and UNDO
An error capturing log on the primary SUSPEND_FROM_CAPTURE The SQL Server error log on the primary
Restart or reconnect mismatch SUSPEND_FROM_RESTART, SUSPEND_FROM_REVALIDATION or SUSPEND_FROM_XRF_UPDATE The error log, as Microsoft indicates for each
Not suspended, but disconnected is_suspended = 0, state still NOT SYNCHRONIZING connected_state_desc and last_connect_error_number in sys.dm_hadr_availability_replica_states

Prove the cause before changing anything

Run the next query on the primary and again on each secondary. The permissions are VIEW SERVER STATE up to SQL Server 2019 and VIEW SERVER PERFORMANCE STATE from SQL Server 2022.

SELECT ag.name AS ag_name,
       ar.replica_server_name,
       adc.database_name,
       drs.is_local,
       drs.synchronization_state_desc,
       drs.synchronization_health_desc,
       drs.database_state_desc,
       drs.is_suspended,
       drs.suspend_reason_desc,
       drs.log_send_queue_size,
       drs.log_send_rate,
       drs.redo_queue_size,
       drs.redo_rate,
       drs.last_redone_time,
       drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id
JOIN sys.availability_replicas AS ar ON ar.replica_id = drs.replica_id
JOIN sys.availability_databases_cluster AS adc
     ON adc.group_database_id = drs.group_database_id
ORDER BY ag.name, ar.replica_server_name, adc.database_name;

Read it this way. is_suspended = 1 with SUSPEND_FROM_USER means a person did it; resume is the fix. is_suspended = 1 with any other reason means an error caused it, so read the error log first. is_suspended = 0 with NOT SYNCHRONIZING points at connection or transition. Microsoft gives secondary_lag_seconds as 0 while data movement is suspended, so a lag of 0 beside a suspended database is not good news.

For the connection case, use the query on Microsoft's error 35250 page against sys.dm_hadr_availability_replica_states. That page is written for secondaries that cannot join the group because they cannot reach the endpoint; its checks are used here for a suspended or disconnected database as well, as advice.

SELECT r.replica_server_name,
       r.endpoint_url,
       rs.connected_state_desc,
       rs.last_connect_error_description,
       rs.last_connect_error_number,
       rs.last_connect_error_timestamp
FROM sys.dm_hadr_availability_replica_states AS rs
     INNER JOIN sys.availability_replicas AS r
         ON rs.replica_id = r.replica_id
WHERE rs.is_local = 1;

Microsoft shows one example result in last_connect_error_description: "DNS Lookup failed with error '11001(No such host is known)'". That page's other checks are the endpoint state (STARTED), connection to the endpoint port with Test-NetConnection (TcpTestSucceeded: True or False), a firewall or antivirus rule blocking port 5022 by default, another process holding the port, name resolution in both directions, and CONNECT permission on the endpoint for the service account.

Remember how the view is laid out. On a secondary it returns a row for every secondary database on that instance. On the primary it returns a row for each primary database and an extra row for the corresponding secondary database. A row that looks wrong on the primary may simply be the primary's partial picture of a secondary, so compare it with the secondary's own row before acting.

If is_suspended is 0 and there is no connection error, the database may be in one of the transition states Microsoft describes, during start-up or a role switch. After a restart or failover, wait and query again before treating it as a fault.

Do not resume yet. Work out why it stopped.

Fix

Suspended by a person (SUSPEND_FROM_USER). Confirm the maintenance that caused it has finished, then resume on the instance that hosts the database. Microsoft documents that you must be connected to the instance hosting the database, that the availability group must be online, and that the primary database must be online. A locally suspended secondary must be resumed locally.

Risk: expect catch-up traffic after a resume, because the secondary has to receive and apply the log queued since the suspension; plan for network and disk load on both replicas and watch the primary's users if the backlog is large. Microsoft says RESUME returns as soon as the replica accepts it and the actual resume happens asynchronously, so a success message does not mean the database is synchronising. Back-out: you can suspend again with ALTER DATABASE ... SET HADR SUSPEND, but the primary log keeps growing while suspended.

ALTER DATABASE [SalesDb] SET HADR RESUME;

Error-driven suspension (REDO, APPLY, CAPTURE, UNDO, RESTART, REVALIDATION, XRF_UPDATE). Resume does not repair the underlying fault. Read the SQL Server error log on the replica named for the reason, fix what it says, such as full disk, I/O error or permission, and only then resume. If the same reason returns after a resume, stop resuming and escalate.

Disconnected. Fix what the connection query and the 35250 checks point to: endpoint not started, wrong port or endpoint URL, a blocked firewall rule, or name resolution. Risk: Microsoft's endpoint restart (ALTER ENDPOINT ... STATE = STOPPED then STARTED) can cause a momentary outage for the replica; do it only when the endpoint is started but replicas still are not talking, and in a quiet window. Back-out: set the endpoint back to STARTED.

If the primary log is filling. Microsoft's suspend page gives three options: add log space for the primary, resume the secondary database before the log fills, or remove the secondary database. It refers readers who hit the full log to its page on error 9002. Removing a secondary database is the last resort. Microsoft's pages treat adding it back as a separate task, so plan for that before you remove anything.

Confirm the fix

Run the same query again on both replicas. The database should show SYNCHRONIZING, or SYNCHRONIZED for a synchronous-commit secondary that has caught up. is_suspended should be 0 and suspend_reason_desc empty. log_send_queue_size and redo_queue_size should fall over successive runs. Microsoft says dividing log_send_queue_size by log_send_rate gives a rough estimate of when the secondary catches up.

Example (illustrative): sql01 is the primary and sql02 the secondary for SalesDb. A maintenance step suspended data movement and nobody resumed it. The primary shows log_send_queue_size of 25,165,824 KB, which is 24 GiB (25,165,824 divided by 1,024 twice). The last active log_send_rate was 51,200 KB per second, or 50 MiB per second. The catch-up time is about 25,165,824 divided by 51,200, or 491.5 seconds, a little over 8 minutes, assuming the rate holds and new log does not arrive faster. The state moves to SYNCHRONIZING first, then to SYNCHRONIZED if sql02 is synchronous commit.

What to watch afterwards

  • Alert on is_suspended = 1 and on synchronization_health_desc other than HEALTHY. Microsoft's troubleshooting page offers a sample query for synchronization_state_desc.
  • Watch primary log use while any secondary is suspended, because the log cannot be truncated until it resumes.
  • Check that redo keeps up. Microsoft's page on changes not appearing on a secondary lists long-running transactions, network throughput and a blocked redo thread as other causes of a secondary that is behind.
  • After patching or restarts, confirm each database is back to SYNCHRONIZING, not left suspended.

Risks and limits

These commands have not been run or lab-tested. The DMV page and the suspend and resume pages describe SQL Server on Windows. A resume does not fix an error that caused a suspension, and a suspended primary database stops data movement to every secondary. Do not remove a database from the group to clear a status without a plan for rebuilding its secondary.

Sources