Availability Group database is not healthy

What this check looks for

When SERVERPROPERTY('IsHadrEnabled') is 1, every row in sys.dm_hadr_database_replica_states where any of these is true:

• is_suspended = 1, meaning data movement has been stopped for that database on that replica.
• synchronization_state_desc is anything other than SYNCHRONIZED or SYNCHRONIZING.
• database_state_desc is not ONLINE.

The message names the group, the database, the replica and the state, and when movement is suspended it includes the suspend_reason_desc SQL Server recorded.

Why it matters

A suspended secondary is the worst of both worlds: it protects nothing, and it still costs you something.

The Availability Group keeps existing. It keeps reporting a group name, the dashboard keeps drawing it, and a failover target that is hours or days stale still looks like a failover target. Nothing about the shape of the configuration changes when data movement stops.

Meanwhile, while a secondary is behind, the primary cannot truncate its transaction log. log_reuse_wait_desc on the primary reads AVAILABILITY_REPLICA and the log grows until the drive fills. That outage lands on the primary server, which is not the machine anybody is looking at, so the investigation starts in the wrong place.

Data movement is usually suspended by a person, for a good short-term reason: a bulk load, an index rebuild, a maintenance window where the secondary could not keep up. The reason is almost always sound and the resumption is almost always forgotten.

How to confirm it yourself

SELECT ag.[name]                        AS [availability_group],
       DB_NAME(drs.[database_id])       AS [database_name],
       ar.[replica_server_name],
       drs.[synchronization_state_desc],
       drs.[synchronization_health_desc],
       drs.[database_state_desc],
       drs.[is_suspended],
       drs.[suspend_reason_desc],
       drs.[log_send_queue_size]  / 1024 AS [send_queue_mb],
       drs.[redo_queue_size]      / 1024 AS [redo_queue_mb]
  FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
 INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK)
         ON ar.[replica_id] = drs.[replica_id]
 INNER JOIN sys.availability_groups AS ag WITH (NOLOCK)
         ON ag.[group_id] = ar.[group_id]
 ORDER BY ag.[name], [database_name], ar.[replica_server_name];

And on the primary, to see whether the log is already being held:

SELECT [name], [log_reuse_wait_desc]
  FROM sys.databases WITH (NOLOCK)
 WHERE [log_reuse_wait_desc] = 'AVAILABILITY_REPLICA';

How to fix it

Resume data movement, on the replica where it is suspended:

ALTER DATABASE [YourDatabase] SET HADR RESUME;

Expect the secondary to be busy for a while afterwards, catching up on everything that accumulated while it was stopped. Watch redo_queue_size come down rather than assuming the resume finished the job.

If the database is no longer meant to be in the group, remove it rather than leaving it suspended:

ALTER DATABASE [YourDatabase] SET HADR OFF;

Removing it is what actually releases the primary’s transaction log. Leaving a database suspended “for now” is what fills the log drive.

If the state is NOT SYNCHRONIZING rather than suspended, the problem is usually connectivity or the endpoint rather than the database. Check the replica’s connected_state_desc in sys.dm_hadr_availability_replica_states and the error log on both ends.

How long it takes

About two hours, most of which is the secondary catching up rather than you working. The decision, resume or remove, takes minutes.

Related reports

Related checks

Frequently asked questions

The dashboard says the group is healthy. Why does this fire? Group health and database health are different things. A group with one suspended database can still report itself as up.

Is SYNCHRONIZING a problem? No, and it is not reported. It is the normal state for an asynchronous replica. NOT SYNCHRONIZING is the one that matters.

Nothing here is suspended, but a database is listed. Then it is the synchronization state or the database state. Read the message: it says which one, and gives the value SQL Server reported.

Why does the check only run on some instances? It is guarded by SERVERPROPERTY('IsHadrEnabled'), so it does nothing at all on an instance without Always On enabled.