--- title: "sys.dm_hadr_availability_replica_states (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "10/16/2017" ms.prod: sql ms.reviewer: "" ms.technology: system-objects ms.topic: "language-reference" f1_keywords: - "dm_hadr_availability_replica_states" - "sys.dm_hadr_availability_replica_states_TSQL" - "sys.dm_hadr_availability_replica_states" - "dm_hadr_availability_replica_states_TSQL" dev_langs: - "TSQL" helpviewer_keywords: - "Availability Groups [SQL Server], monitoring" - "sys.dm_hadr_availability_replica_states dynamic management view" ms.assetid: d2e678bb-51e8-4a61-b223-5c0b8d08b8b1 author: MikeRayMSFT ms.author: mikeray --- # sys.dm_hadr_availability_replica_states (Transact-SQL) [!INCLUDE[tsql-appliesto-ss2012-xxxx-xxxx-xxx-md](../../includes/tsql-appliesto-ss2012-xxxx-xxxx-xxx-md.md)] Returns a row for each local replica and a row for each remote replica in the same Always On availability group as a local replica. Each row contains information about the state of a given replica. > [!IMPORTANT] > To obtain information about every replica in a given availability group, query **sys.dm_hadr_availability_replica_states** on the server instance that is hosting the primary replica. When queried on a server instance that is hosting a secondary replica of an availability group, this dynamic management view returns only local information for the availability group. |Column name|Data type|Description| |-----------------|---------------|-----------------| |**replica_id**|**uniqueidentifier**|Unique identifier of the replica.| |**group_id**|**uniqueidentifier**|Unique identifier of the availability group.| |**is_local**|**bit**|Whether the replica is local, one of:

0 = Indicates a remote secondary replica in an availability group whose primary replica is hosted by the local server instance. This value occurs only on the primary replica location.

1 = Indicates a local replica. On secondary replicas, this is the only available value for the availability group to which the replica belongs.| |**role**|**tinyint**|Current [!INCLUDE[ssHADR](../../includes/sshadr-md.md)] role of a local replica or a connected remote replica, one of:

0 = Resolving

1 = Primary

2 = Secondary

For information about [!INCLUDE[ssHADR](../../includes/sshadr-md.md)] roles, see [Overview of Always On Availability Groups (SQL Server)](../../database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server.md).| |**role_desc**|**nvarchar(60)**|Description of **role**, one of:

RESOLVING

PRIMARY

SECONDARY| |**operational_state**|**tinyint**|Current operational state of the replica, one of:

0 = Pending failover

1 = Pending

2 = Online

3 = Offline

4 = Failed

5 = Failed, no quorum

NULL = Replica is not local.

For more information, see [Roles and Operational States](#RolesAndOperationalStates), later in this topic.| |**operational\_state\_desc**|**nvarchar(60)**|Description of **operational\_state**, one of:

PENDING_FAILOVER

PENDING

ONLINE

OFFLINE

FAILED

FAILED_NO_QUORUM

NULL| |**recovery\_health**|**tinyint**|Rollup of the **database\_state** column of the [sys.dm_hadr_database_replica_states](../../relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql.md) dynamic management view. The following are the possible values and their descriptions.

0 : In progress. At least one joined database has a database state other than ONLINE (**database\_state** is not 0).

1 : Online. All the joined databases have a database state of ONLINE (**database_state** is 0).

NULL : **is_local** = 0| |**recovery_health_desc**|**nvarchar(60)**|Description of **recovery_health**, one of:

ONLINE_IN_PROGRESS

ONLINE

NULL| |**synchronization\_health**|**tinyint**|Reflects a rollup of the database synchronization state (**synchronization_state**)of all joined availability databases (also known as *replicas*) and the availability mode of the replica (synchronous-commit or asynchronous-commit mode). The rollup will reflect the least healthy accumulated state the databases on the replica. Below are the possible values and their descriptions.

0 : Not healthy. At least one joined database is in the NOT SYNCHRONIZING state.

1 : Partially healthy. Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing.

2 : Healthy. All replicas are in the target synchronization state: synchronous-commit replicas are synchronized, and asynchronous-commit replicas are synchronizing.| |**synchronization_health_desc**|**nvarchar(60)**|Description of **synchronization_health**, one of:

NOT_HEALTHY

PARTIALLY_HEALTHY

HEALTHY| |**connected_state**|**tinyint**|Whether a secondary replica is currently connected to the primary replica. The possible values are shown below with their descriptions.

0 : Disconnected. The response of an availability replica to the DISCONNECTED state depends on its role: On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as NOT SYNCHRONIZED on the primary replica, which waits for the secondary to reconnect; On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.

1 : Connected.

Each primary replica tracks the connection state for every secondary replica in the same availability group. Secondary replicas track the connection state of only the primary replica.| |**connected_state_desc**|**nvarchar(60)**|Description of **connection_state**, one of:

DISCONNECTED

CONNECTED| |**last_connect_error_number**|**int**|Number of the last connection error.| |**last_connect_error_description**|**nvarchar(1024)**|Text of the **last_connect_error_number** message.| |**last_connect_error_timestamp**|**datetime**|Date and time timestamp indicating when the **last_connect_error_number** error occurred.| ## Roles and Operational States The role, **role**, reflects the state of a given availability replica and the operational state, **operational_state**, describes whether the replica is ready to process client requests for all the database of the availability replica. The following is a summary of the operational states that are possible for each role: RESOLVING, PRIMARY, and SECONDARY. **RESOLVING:** When an availability replica is in the RESOLVING role, the possible operational states are as shown in the following table. |Operational State|Description| |-----------------------|-----------------| |PENDING_FAILOVER|A failover command is being processed for the availability group.| |OFFLINE|All configuration data for the availability replica has been updated on WSFC cluster and, also, in local metadata, but the availability group currently lacks a primary replica.| |FAILED|A read failure has occurred during an attempt trying to retrieve information from the WSFC cluster.| |FAILED_NO_QUORUM|The local WSFC node does not have quorum. This is an inferred state.| **PRIMARY:** When an availability replica is performing the PRIMARY role, it is currently the primary replica. The possible operational states are as shown in the following table. |Operational State|Description| |-----------------------|-----------------| |PENDING|This is a transient state, but a primary replica can be stuck in this state if workers are not available to process requests.| |ONLINE|The availability group resource is online, and all database worker threads have been picked up.| |FAILED|The availability replica is unable to read to and/or write from the WSFC cluster.| **SECONDARY:** When an availability replica is performing the SECONDARY role, it is currently a secondary replica. The possible operational states are as shown in the table below. |Operational State|Description| |-----------------------|-----------------| |ONLINE|The local secondary replica is connected to the primary replica.| |FAILED|The local secondary replica is unable to read to and/or write from the WSFC cluster.| |NULL|On a primary replica, this value is returned when the row relates to a secondary replica.| ## Security ### Permissions Requires VIEW SERVER STATE permission on the server. ## See Also [Overview of Always On Availability Groups (SQL Server)](../../database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server.md) [Monitor Availability Groups (Transact-SQL)](../../database-engine/availability-groups/windows/monitor-availability-groups-transact-sql.md)