What’s the issue?
In an Always On Availability Group, data movement is the process by which transaction log records flow from the primary replica to each secondary replica, keeping the secondary databases synchronized with the primary. SQL Server tracks the data movement state for every database on every replica, and this state can be either resumed (the normal operating state) or suspended.
This finding identifies AG databases where data movement has been suspended on at least one replica, meaning the secondary copy is no longer receiving updates from the primary. The condition can result from a manual ALTER DATABASE … SET HADR SUSPEND command, automatic suspension by SQL Server in response to specific errors, or operations such as restoring a backup of a different database with the same name on the secondary.
The result is that the affected secondary copy is increasingly out of sync with the primary for as long as the suspension persists.
Why is this a problem?
A suspended secondary cannot be used for failover until data movement is resumed and the secondary catches up. If the primary fails while a secondary is suspended, automatic failover to that secondary is not possible, and a manual failover would result in significant data loss equal to whatever transactions occurred since the suspension began.
A suspended secondary also affects the transaction log on the primary. Log records that have not yet been delivered to all secondaries cannot be truncated, so the primary’s log file can grow continuously while the suspension persists. If the suspension is not resolved promptly, the primary’s log volume can fill, causing the entire AG to stop accepting writes.
Backup strategies that depend on secondary replicas are also disrupted. If the AG backup preference points to a secondary that is suspended, backup jobs may either skip entirely or run against an out of date copy of the database, producing recovery points that do not reflect actual current data.
What should you do about this?
Identify suspended databases by querying sys.dm_hadr_database_replica_states and reviewing the is_suspended and suspend_reason_desc columns for every database on every replica. Capture the reason for each suspension, since the appropriate remediation depends on what caused it.
For suspensions caused by manual action that is no longer needed, resume data movement using ALTER DATABASE [DatabaseName] SET HADR RESUME; on the affected secondary. The secondary will begin catching up automatically, and the rate depends on the volume of log records accumulated since the suspension began.
For suspensions caused by errors such as log file size limits, disk space issues, or specific replication failures, address the underlying cause first. Resolve the disk space, log size, or other issue, then resume data movement. Confirm the secondary catches up successfully and that synchronization state returns to healthy.
Monitor primary log file size and AG synchronization state during the catch up period, since a long suspension can leave significant log to replay and the primary’s log will not begin truncating until all secondaries are current.
Read more…
Resume an availability group database – SQL Server Always On | Microsoft Learn