Edit

Share via

sys.dm_hadr_availability_replica_states (Transact-SQL)

Applies to: SQL Server

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 get information about every replica in a given availability group, query sys.dm_hadr_availability_replica_states on the server instance that hosts the primary replica. When you query this dynamic management view on a server instance that hosts a secondary replica of an availability group, it returns only local information for the availability group.

Column name Data type Nullable Description
replica_id uniqueidentifier No Unique identifier of the replica.
group_id uniqueidentifier No Unique identifier of the availability group.
is_local bit No 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 Yes Current Always On availability groups role of a local replica or a connected remote replica, one of:

0 = Resolving
1 = Primary
2 = Secondary

For information about Always On availability groups roles, see What is an Always On availability group?
role_desc nvarchar(60) Yes Description of role, one of:

RESOLVING
PRIMARY
SECONDARY
operational_state tinyint Yes 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 isn't local.

For more information, see Roles and Operational States, later in this article.
operational_state_desc nvarchar(60) Yes Description of operational_state, one of:

PENDING_FAILOVER

PENDING

ONLINE

OFFLINE

FAILED

FAILED_NO_QUORUM

NULL
connected_state tinyint Yes 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's 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) Yes Description of connection_state, one of:

DISCONNECTED
CONNECTED
recovery_health tinyint Yes Rollup of the database_state column of the sys.dm_hadr_database_replica_states 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 isn't 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) Yes Description of recovery_health, one of:

ONLINE_IN_PROGRESS
ONLINE
NULL
synchronization_health tinyint Yes 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 aren't 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) Yes Description of synchronization_health, one of:

NOT_HEALTHY
PARTIALLY_HEALTHY
HEALTHY
last_connect_error_number int Yes Number of the last connection error.
last_connect_error_description nvarchar(1024) Yes Text of the last_connect_error_number message.
last_connect_error_timestamp datetime Yes Date and time timestamp indicating when the last_connect_error_number error occurred.
write_lease_remaining_ticks bigint Yes Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
current_configuration_commit_start_time_utc datetime Yes Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
is_internal bit Yes Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
operational_state_desc nvarchar(60) Yes Description of operational_state, one of:

PENDING_FAILOVER
PENDING
ONLINE
OFFLINE
FAILED
FAILED_NO_QUORUM
NULL
recovery_health tinyint Yes Rollup of the database_state column of the sys.dm_hadr_database_replica_states 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 isn't 0).

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

NULL: is_local isn't 0
synchronization_health tinyint No 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 reflects the least healthy accumulated state the databases on the replica. The possible values and their descriptions are:

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

1 = Partially healthy. Some replicas aren't 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.

Roles and operational states

The role, role, reflects the state of a given availability replica. The operational state, operational_state, describes whether the replica is ready to process client requests for all the databases of the availability replica. The following table summarizes 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 The system is processing a failover command for the availability group.
OFFLINE All configuration data for the availability replica is updated on WSFC cluster and, also, in local metadata, but the availability group currently lacks a primary replica.
FAILED A read failure occurred during an attempt to retrieve information from the WSFC cluster.
FAILED_NO_QUORUM The local WSFC node doesn't have quorum. This state is inferred.

PRIMARY: When an availability replica performs the PRIMARY role, it's currently the primary replica. The possible operational states are as shown in the following table.

Operational state Description
PENDING This state is transient, but a primary replica can be stuck in this state if workers aren't available to process requests.
ONLINE The availability group resource is online, and all database worker threads have been picked up.
FAILED The availability replica can't read to or write from the WSFC cluster.

SECONDARY: When an availability replica performs the SECONDARY role, it's 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 can't read to or write from the WSFC cluster.
NULL On a primary replica, this value is returned when the row relates to a secondary replica.

Permissions

SQL Server 2019 (15.x) and earlier versions require VIEW SERVER STATE permission on the server.

SQL Server 2022 (16.x) and later versions require VIEW SERVER PERFORMANCE STATE permission on the server.

Related content

  • Last updated on