| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | |||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.dm_hadr_availability_group_states (Transact-SQL) |
sys.dm_hadr_availability_group_states returns a row for each AG that possesses an availability replica on the local instance of SQL Server. |
rwestMSFT |
randolphwest |
04/17/2023 |
sql |
system-objects |
reference |
|
|
|
[!INCLUDE SQL Server]
Returns a row for each Always On availability group that possesses an availability replica on the local instance of [!INCLUDEssNoVersion]. 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 |
For [!INCLUDE sssql19-md] and earlier versions, requires VIEW SERVER STATE permission on the server.
For [!INCLUDE sssql22-md] and later versions, requires VIEW SERVER PERFORMANCE STATE permission on the server.