Edit

Share via

sys.dm_hadr_database_replica_states (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

Returns a row for each database that is participating in an Always On availability group for which the local instance of SQL Server is hosting an availability replica. This dynamic management view exposes state information on both the primary and secondary replicas. On a secondary replica, this view returns a row for every secondary database on the server instance. On the primary replica, this view returns a row for each primary database and an extra row for the corresponding secondary database.

Important

Depending on the action and higher-level states, database-state information might be unavailable or out of date. Furthermore, the values have only local relevance. For example, on the primary replica, the value of the last_hardened_lsn column reflects the information about a given secondary database that is currently available to the primary replica, not the actual hardened log sequence number (LSN) that the secondary replica might have currently.

Column name Data type Description (on primary replica)
database_id int Identifier of the database, unique within an instance of SQL Server. This is the same value as displayed in the sys.databases catalog view.
group_id uniqueidentifier Identifier of the availability group to which the database belongs.
replica_id uniqueidentifier Identifier of the availability replica within the availability group.
group_database_id uniqueidentifier Identifier of the database within the availability group. This identifier is identical on every replica to which this database is joined.
is_local bit Whether the availability database is local, one of:

0 = The database isn't local to the SQL Server instance.
1 = The database is local to the server instance.
is_primary_replica bit Returns 1 if the replica is primary, or 0 if it's a secondary replica.

Applies to: SQL Server 2014 (12.x) and later versions.
synchronization_state tinyint Data-movement state, one of the following values.

0 = Not synchronizing. For a primary database, indicates that the database isn't ready to synchronize its transaction log with the corresponding secondary databases. For a secondary database, this value indicates that the database hasn't started log synchronization because of a connection issue, is being suspended, or is going through transition states during startup or a role switch.

1 = Synchronizing. For a primary database, indicates that the database is ready to accept a scan request from a secondary database. For a secondary database, indicates that active data movement is occurring for the database.

2 = Synchronized. A primary database shows SYNCHRONIZED in place of SYNCHRONIZING. A synchronous-commit secondary database shows synchronized when the local cache says the database is failover ready and is synchronizing.

3 = Reverting. Indicates the phase in the undo process when a secondary database is actively getting pages from the primary database.

Caution: When a database on a secondary replica is in the REVERTING state, forcing failover to the secondary replica leaves the database in a state in which it can't be started as a primary database. Either the database needs to reconnect as a secondary database, or you need to apply new log records from a log backup.

4 = Initializing. Indicates the phase of undo when the transaction log required for a secondary database to catch up to the undo LSN is being shipped and hardened on a secondary replica.

Caution: When a database on a secondary replica is in the INITIALIZING state, forcing failover to the secondary replica leaves the database in a state in which it can't be started as a primary database. Either the database needs to reconnect as a secondary database, or you need to apply new log records from a log backup.
synchronization_state_desc nvarchar(60) Description of the data-movement state, one of:

NOT SYNCHRONIZING
SYNCHRONIZING
SYNCHRONIZED
REVERTING
INITIALIZING
is_commit_participant bit 0 = Transaction commit isn't synchronized with respect to this database.

1 = Transaction commit is synchronized with respect to this database.

For a database on an asynchronous-commit availability replica, this value is always 0.

For a database on a synchronous-commit availability replica, this value is accurate only on the primary database.
synchronization_health tinyint Reflects the intersection of the synchronization state of a database that is joined to the availability group on the availability replica, and the availability mode of the availability replica (synchronous-commit or asynchronous-commit mode). Can be one of the following values.

0 = Not healthy. The synchronization_state of the database is 0 (NOT SYNCHRONIZING).

1 = Partially healthy. A database on a synchronous-commit availability replica is considered partially healthy if synchronization_state is 1 (SYNCHRONIZING).

2 = Healthy. A database on a synchronous-commit availability replica is considered healthy if synchronization_state is 2 (SYNCHRONIZED), and a database on an asynchronous-commit availability replica is considered healthy if synchronization_state is 1 (SYNCHRONIZING).
synchronization_health_desc nvarchar(60) Description of the synchronization_health of the availability database.

NOT_HEALTHY
PARTIALLY_HEALTHY
HEALTHY
database_state tinyint 0 = Online
1 = Restoring
2 = Recovering
3 = Recovery pending
4 = Suspect
5 = Emergency
6 = Offline

Note: Same as state column in sys.databases.
database_state_desc nvarchar(60) Description of the database_state of the availability replica.

ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
EMERGENCY
OFFLINE

Note: Same as state_desc column in sys.databases.
is_suspended bit Database state, one of:

0 = Resumed
1 = Suspended
suspend_reason tinyint If the database is suspended, the reason for the suspended state, one of:

0 = User action
1 = Suspend from partner
2 = Redo
3 = Capture
4 = Apply
5 = Restart
6 = Undo
7 = Revalidation
8 = Error in the calculation of the secondary-replica synchronization point
suspend_reason_desc nvarchar(60) Description of the database suspended state reason, one of:

SUSPEND_FROM_USER = A user manually suspended data movement

SUSPEND_FROM_PARTNER = The database replica is suspended after a forced failover

SUSPEND_FROM_REDO = An error occurred during the redo phase

SUSPEND_FROM_APPLY = An error occurred when writing the log to file (see error log)

SUSPEND_FROM_CAPTURE = An error occurred while capturing the log on the primary replica

SUSPEND_FROM_RESTART = The database replica was suspended before the database was restarted (see error log)

SUSPEND_FROM_UNDO = An error occurred during the undo phase (see error log)

SUSPEND_FROM_REVALIDATION = Log change mismatch is detected on reconnection (see error log)

SUSPEND_FROM_XRF_UPDATE = Unable to find the common log point (see error log)
recovery_lsn numeric(25,0) On the primary replica, the end of the transaction log before the primary database writes any new log records after recovery or failover. For a given secondary database, if this value is less than the current hardened LSN (last_hardened_lsn), recovery_lsn is the value to which this secondary database would need to resynchronize (that is, to revert to and reinitialize to). If this value is greater than or equal to the current hardened LSN, resynchronization would be unnecessary and wouldn't occur.

The recovery_lsn reflects a log-block ID padded with zeroes. It isn't an actual LSN. For information about how this value is derived, see Understand the LSN column values, later in this article.
truncation_lsn numeric(25,0) On the primary replica, for the primary database, reflects the minimum log truncation LSN across all the corresponding secondary databases. If local log truncation is blocked (such as by a backup operation), this LSN might be higher than the local truncation LSN.

For a given secondary database, reflects the truncation point of that database.
truncation_lsn reflects a log-block ID padded with zeroes. It isn't an actual log sequence number.
last_sent_lsn numeric(25,0) When you query the primary replica, last_sent_lsn is reported for each secondary replica database row. The log block identifier that indicates the point up to which all log blocks have been sent by the primary. This is the ID of the next log block that is sent, rather than the ID of the most recently sent log block.

last_sent_lsn reflects a log-block ID padded with zeroes. It isn't an actual log sequence number.
last_sent_time datetime When you query the primary replica, last_sent_time is reported for each secondary replica database row. Time when the last log block was sent.
last_received_lsn numeric(25,0) When you query a secondary replica, last_received_lsn is reported for the local secondary replica database row. Log block ID identifying the point up to which all log blocks have been received by the secondary replica that hosts this secondary database.

The last_received_lsn reflects a log-block ID padded with zeroes. It isn't an actual log sequence number.
last_received_time datetime When you query a secondary replica, last_received_time is reported for the local secondary replica database row. Time when the log block ID in last message received was read on the secondary replica.
last_hardened_lsn numeric(25,0) Start of the log block containing the log records of last hardened LSN on a secondary database.

On an asynchronous-commit primary database, or on a synchronous-commit database whose current policy is delay, the value is NULL. For other synchronous-commit primary databases, last_hardened_lsn indicates the minimum of the hardened LSN across all the secondary databases.

Note: the last_hardened_lsn reflects a log-block ID padded with zeroes. It isn't an actual log sequence number. For more information, see Understand the LSN column values, later in this article.
last_hardened_time datetime On a secondary database, time of the log-block identifier for the last hardened LSN (last_hardened_lsn). On a primary database, reflects the time corresponding to minimum hardened LSN.
last_redone_lsn numeric(25,0) Actual log sequence number of the last log record that was redone on the secondary database. The last_redone_lsn is always less than last_hardened_lsn.
last_redone_time datetime Time when the last log record was redone on the secondary database.
log_send_queue_size bigint Number of log records of the primary database that hasn't been sent to the secondary databases, in kilobytes (KB).
log_send_rate bigint Average rate at which primary replica instance sent data during last active period, in kilobytes (KB)/second.
redo_queue_size bigint Number of log records in the log files of the secondary replica that aren't yet redone, in kilobytes (KB).
redo_rate bigint Average rate at which the log records are being redone on a given secondary database, in kilobytes (KB)/second.

redo_rate is calculated by dividing the total log bytes redone since database engine startup by the time span when redo was actively running, rather than by the elapsed time. Because redo might not be running continuously, resulting value might be different (higher) than the value of Database Replica:Redone Bytes/sec performance counter.
filestream_send_rate bigint Specifies the rate at which the FILESTREAM files are shipped to the secondary replica, in kilobytes (KB)/second.
end_of_log_lsn numeric(25,0) Local end of log LSN. Actual LSN corresponding to the last log record in the log cache on the primary and secondary databases. On the primary replica, the secondary rows reflect the end of log LSN from the latest progress messages that the secondary replicas sent to the primary replica.

end_of_log_lsn reflects a log-block ID padded with zeroes. It isn't an actual log sequence number. For more information, see Understand the LSN column values, later in this article.
last_commit_lsn numeric(25,0) Actual log sequence number corresponding to the last commit record in the transaction log.

On the primary database, this corresponds to the last commit record processed. Rows for secondary databases show the log sequence number that the secondary replica sent to the primary replica.
On the secondary replica, this is the last commit record that was redone.
last_commit_time datetime Time corresponding to the last commit record.

On the secondary database, this time is the same as on the primary database.

On the primary replica, each secondary database row displays the time that the secondary replica that hosts the secondary database reported back to the primary replica. The difference in time between the primary-database row and a given secondary-database row represents approximately the recovery point objective (RPO), assuming that the redo process is caught up and that the progress was reported back to the primary replica by the secondary replica.
low_water_mark_for_ghosts bigint A monotonically increasing number for the database indicating a low water mark used by ghost cleanup on the primary database. If this number isn't increasing over time, it implies that ghost cleanup might not happen. To decide which ghost rows to clean up, the primary replica uses the minimum value of this column for this database across all availability replicas (including the primary replica).
secondary_lag_seconds bigint The number of seconds that the secondary replica is behind the primary replica during synchronization.

On the primary replica, the synchronization delay (lag) for each secondary database is calculated as the number of seconds since the hardening of the the earliest hardened LSN on the primary replica that is yet to be hardened on the secondary replica. This value shows as 0 if the data movement is suspended. The data movement needs to be in a non suspended state in order for this value to show active lag.

Applies to: SQL Server 2016 (13.x) and later versions.

Understand the LSN column values

The values of the end_of_log_lsn, last_hardened_lsn, last_received_lsn, last_sent_lsn, recovery_lsn, and truncation_lsn columns aren't actual log sequence numbers (LSNs). Rather each of these values reflects a log-block ID padded with zeroes.

end_of_log_lsn, last_hardened_lsn, and recovery_lsn are flush LSNs. For example, last_hardened_lsn indicates the start of the next block past the blocks that are already on disk. So any LSN less the value of last_hardened_lsn is on disk. LSNs that are greater than or equal to this value aren't flushed.

Of the LSN values returned by sys.dm_hadr_database_replica_states, only last_redone_lsn is a real LSN.

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