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, 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 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. Below are the possible values and their descriptions. 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 (Transact-SQL). |
| synchronization_health_desc | nvarchar(60) | Description of synchronization_health, one of: NOT_HEALTHY PARTIALLY_HEALTHY HEALTHY |
Security
Permissions
Requires VIEW SERVER STATE permission on the server.
See Also
Monitor Availability Groups (Transact-SQL)
Always On Availability Groups (SQL Server)
Always On Availability Groups Dynamic Management Views and Functions (Transact-SQL)