sys.dm_hadr_availability_group_states (Transact-SQL)
Applies to: 
SQL Server
Returns a row for each Always On availability group that possesses an availability replica on the local instance of SQL Server. Each row displays the states that define the health of a given availability group.
Note
To obtain the complete list of availability groups, query the sys.availability_groups catalog view.
| Column name | Data type | Description |
|---|---|---|
| group_id | uniqueidentifier | Unique identifier of the availability group. |
| primary_replica | varchar(128) | Name of the server instance that is hosting the current primary replica. NULL = Not the primary replica and unable to communicate with the WSFC failover cluster. |
| primary_recovery_health | tinyint | Indicates the recovery health of the primary replica, one of: 0 = In progress 1 = Online NULL On secondary replicas, the primary_recovery_health column is NULL. |
| primary_recovery_health_desc | nvarchar(60) | Description of primary_replica_health, one of: ONLINE_IN_PROGRESS ONLINE NULL |
| secondary_recovery_health | tinyint | Indicates the recovery health of a secondary replica, one of: 0 = In progress 1 = Online NULL On the primary replica, the secondary_recovery_health column is NULL. |
| secondary_recovery_health_desc | nvarchar(60) | Description of secondary_recovery_health, one of: ONLINE_IN_PROGRESS ONLINE NULL |
| synchronization_health | tinyint | Reflects a rollup of the synchronization_health of all availability replicas in the availability group. The possible values and their descriptions are as follows: 0: Not healthy. None of the availability replicas have a healthy synchronization_health (2 = HEALTHY). 1: Partially healthy. The synchronization health of some, but not all, availability replicas is healthy. 2: Healthy. The synchronization health of every availability replica is healthy. For information about replica synchronization health, see the synchronization_health column in sys.dm_hadr_availability_replica_states. |
| synchronization_health_desc | nvarchar(60) | Description of synchronization_health, one of: NOT_HEALTHY PARTIALLY_HEALTHY HEALTHY |
Permissions
For SQL Server 2019 (15.x) and earlier versions, requires VIEW SERVER STATE permission on the server.
For SQL Server 2022 (16.x) and later versions, requires VIEW SERVER PERFORMANCE STATE permission on the server.
See also
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for