Shulou(Shulou.com)06/01 Report--

18 、 Sys. Dm_hadr_database_replica_states

The local instance SQL Server that returns the rows participating in each database in the AlwaysOn availability group is hosting its availability copy. This dynamic management view exposes status information related to primary and secondary replicas. On the secondary copy, this view returns a row for each secondary database on the server instance. On the primary copy, this view returns a row for each primary database and another row for the corresponding secondary database.

important

Depending on the operation and higher-level status, the database state information may be unavailable or out of date. In addition, these values have local correlation only. For example, the value last_hardened_lsn column in the primary copy reflects information about the given secondary database currently available in the primary copy, and the secondary copy of LSN values that are not actually forced to write may currently have.

Column name

Data type

Description (for master copy)

Database_id

Int

The identifier of the database is unique within the instance of SQL Server. This is the Sys.databases catalog view shown in the same value.

Group_id

Uniqueidentifier

The identifier of the availability group to which the database belongs.

Replica_id

Uniqueidentifier

Identifier of the availability copy within the availability group.

Group_database_id

Uniqueidentifier

The identifier of the database within the availability group. The identifier is the same on each copy to which this database is joined.

Is_local

Bit

Whether the availability database is local can be one of the following values:

0 = the database is not local to the SQL Server instance.

1 = database is local to the server instance.

Is_primary_replica

Bit

Returns 1 if the copy is the primary copy and 0 if the copy is the secondary copy.

Scope of application: SQL Server 2014 (12.x) to SQL Server 2017.

Synchronization_state

Synchronization_state_desc

Tinyint

Nvarchar (60)

Data movement status to one of the following values.

0 = NOT SYNCHRONIZING is not synchronized.

1 = SYNCHRONIZING is synchronizing.

2 = SYNCHRONIZED is synchronized.

3 = REVERTING recovery.

4 = INITIALIZING is initializing.

Is_commit_participant

Bit

0 = for this database, the transaction commit is not synchronized.

1 = for the database, the transaction commit is synchronized.

For databases on available replicas committed asynchronously, the value is always 0.

For databases on synchronously committed availability replicas, this value is accurate only on the primary database.

Synchronization_health

Synchronization_health_desc

Tinyint

Nvarchar (60)

The following values reflect the intersection of the synchronous status of the database connected to the availability group on the availability replica and the availability mode of one of the availability replicas (synchronous commit or asynchronous commit mode).

0 = NOT_HEALTHY is abnormal. The database Synchronization_state is 0 (NOT SYNCHRONIZING).

1 = PARTIALLY_HEALTHY is not completely normal. It is considered completely normal if the database synchronization_state 1 (SYNCHRONIZING) on the available copy is committed synchronously.

2 = HEALTHY is normal. It is considered normal if the database synchronization_state on the synchronous commit availability replica is 2 (SYNCHRONIZED) and the database synchronization_state on the asynchronous commit availability replica is 1 (SYNCHRONIZING).

Database_state

Database_state_desc

Tinyint

Nvarchar (60)

0 = ONLINE online

1 = RESTORING is being restored

2 = RECOVERING is recovering

3 = RECOVERY_PENDING recovery pending

4 = SUSPECT is suspicious

5 = EMERGENCY urgent

6 = OFFLINE offline

Note: same as the state and state_desc columns in Sys.databases.

Is_suspended

Bit

Database status, which can be one of the following values:

0 = restored

1 = suspended

Suspend_reason

Suspend_reason_desc

Tinyint

Nvarchar (60)

If the database is in a suspended state, it is the reason for the suspended state and can be one of the following values:

0 = SUSPEND_FROM_USER = receipts that the user manually suspended

1 = SUSPEND_FROM_PARTNER = suspend database copy / suspend from partner after forced failover

2 = SUSPEND_FROM_REDO = error in redo phase

3 = SUSPEND_FROM_CAPTURE = error capturing logs on the master copy

4 = SUSPEND_FROM_APPLY = error writing log to file (see error log)

5 = SUSPEND_FROM_RESTART = suspend the database copy before restarting the database (see error log)

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

7 = revalidate

8 = error calculating the synchronization point of the secondary copy

SUSPEND_FROM_REVALIDATION = mismatch of log changes detected during reconnection (see error log)

SUSPEND_FROM_XRF_UPDATE = Public log point not found (see error log)

Recovery_lsn

Numeric (25Phone0)

On the primary replica, the end of the transaction log after a restore or failover and before any new log records are written to the primary database. For a given secondary database, if the value is less than the currently hardened LSN (last_hardened_lsn), recovery_lsn is the value that the secondary database needs to resynchronize (that is, the value to be restored to and reinitialized). If the value is greater than or equal to the current hardened LSN, resynchronization will not be necessary and will not occur.

Recovery_lsn reflects the log block ID filled with zeros. It is not the actual log serial number (LSN). For information about how to derive this value, see understanding LSN column values, a later version of this topic.

Truncation_lsn

Numeric (25Phone0)

On the primary copy, for the primary database, reflects the minimum log truncated LSN in all corresponding secondary databases. If local log truncation is blocked (for example, by backup operations), the LSN may be higher than the local truncated LSN.

For a given secondary database, the cutoff point of the database is reflected.

Truncation_lsn reflects the log block ID filled with zeros. It is not the actual log serial number.

Last_sent_lsn

Numeric (25Phone0)

A log block identifier that indicates a point at which all log blocks have been sent by the primary database. The identifier is the ID of the next log block to be sent, not the ID of the most recently sent log block.

Last_sent_lsn reflects that the log block ID is populated with zero, which is not the actual log sequence number.

Last_sent_time

Datetime

The time when the last log block was sent.

Last_received_lsn

Numeric (25Phone0)

A log block ID that identifies a point by which all log blocks have been received by the secondary copy that hosts this secondary database.

Last_received_lsn reflects the log block ID filled with zeros. It is not the actual log serial number.

Last_received_time

Datetime

The time the log block ID in the last received message was read on the secondary copy.

Last_hardened_lsn

Numeric (25Phone0)

The beginning of the log block that contains the log record of the last forced write to the LSN on the secondary database.

This value is NULL on an asynchronous commit master database or on a synchronous commit database whose current policy is deferred. For other synchronous commit primary databases, last_hardened_lsn indicates the minimum value of forced LSN in all secondary databases.

Note: last_hardened_lsn reflects the log block ID filled with zeros. It is not the actual log serial number. For more information, see understanding LSN column values, a later version of this topic.

Last_hardened_time

Datetime

The LSN (last_hardened_lsn) forced to write to the last log block identifier on the secondary database. "on the primary database, reflects the time corresponding to the minimum forced write LSN."

Last_redone_lsn

Numeric (25Phone0)

The actual log sequence number of the last log record redone on the secondary database. Last_redone_lsn is always less than last_hardened_lsn.

Last_redone_time

Datetime

The time when the last log record was redone on the secondary database.

Log_send_queue_size

Bigint

The number of log records in the primary database that have not been sent to the secondary database (KB).

Log_send_rate

Bigint

The rate at which instance data is sent at the average master copy of kilobytes (KB) during the last activity.

Redo_queue_size

Bigint

The amount of logging in the log file of the secondary copy that has not been redone (KB).

Redo_rate

Bigint

The rate at which logging is redone in a given secondary database (KB/s).

Filestream_send_rate

Bigint

The rate at which FILESTREAM files are transferred to the secondary copy (in KB/ seconds).

End_of_log_lsn

Numeric (25Phone0)

The local end of the log LSN. The actual LSN corresponding to the last log record in the log cache on the primary and secondary databases. On the primary copy, the secondary line reflects the end of the log LSN in the latest progress message that the secondary copy has been sent to the primary copy.

End_of_log_lsn reflects the log block ID filled with zeros. It is not the actual log serial number. For more information, see understanding LSN column values, a later version of this topic.

Last_commit_lsn

Numeric (25Phone0)

The actual log sequence number corresponding to the last committed record in the transaction log.

On the primary database, this corresponds to the commit record that was last processed. The row of the secondary database shows the log sequence number that the secondary copy has been sent to the primary copy.

On the secondary copy, this is the last commit record that has been redone.

Last_commit_time

Datetime

The time corresponding to the last submitted record.

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

On the primary copy, each secondary database row shows when the secondary copy that hosts the secondary database reports back to the primary copy. The time difference between the primary database row and the given secondary database row represents approximately the recovery point objective (RPO), assuming that the process is redone and progress has been reported by the secondary copy.

Low_water_mark_for_ghosts

Bigint

For the monotonously increasing number of the database, indicates the low watermark used for shadow cleanup on the primary database. If this number does not increase over time, it means that shadow cleanup may not have occurred. To determine which shadow rows to clear, the master copy uses the minimum value of the column for this database on all available copies, including the master copy.

Secondary_lag_seconds

Bigint

The number of seconds that the secondary copy is the primary copy during synchronization.

Scope of application: SQL Server 2016 (13.x) to SQL Server 2017.

Understanding LSN column valu

End_of_log_lsn, last_hardened_lsn, last_received_lsn, last_sent_lsn, recovery _ lsn, and the value of the truncation_lsn column is not the actual log sequence number (Lsn). The above values reflect the log block ID populated with zeros.

End_of_log_lsn, last_hardened_lsn, and recovery_lsn are refresh Lsn. For example, last_hardened_lsn indicates the beginning of the next block that is already on disk. So any LSN= this value will not be refreshed.

If the LSN value Sys.dm_hadr_database_replica_states is returned, only last_redone_lsn is the real LSN.

Status information table of all availability databases under the current instance

Synchronization_state and synchronization_state_desc

Value

Description

0

NOT SYNCHRONIZING is not synchronized.

For a primary database, indicates that the database is not ready to synchronize its transaction log with the corresponding secondary database.

For the secondary database, indicates that the database did not start log synchronization due to connection problems, is being suspended, or is transiting state during startup or role switching.

one

SYNCHRONIZING is synchronizing.

For the primary database, indicates that the database is ready to accept scan requests from the secondary database.

For a secondary database, indicates that active data movement is taking place for that database.

two

Synchronized.

The main database displays SYNCHRONIZED instead of SYNCHRONIZING.

The synchronous commit secondary database will show that it is synchronized if the local cache indicates that the copy of the database is available for failover and that the database is synchronizing.

three

REVERTING is recovering. Indicates the stage in the undo process when the secondary database actively fetches the page from the primary database.

Note: when the database on the secondary replica is in the REVERTING state, forcing a failover to the secondary replica leaves the database in a state where the database cannot be started as the primary database. The database will need to reconnect as a secondary database, or you will need to apply new log records from the log backup.

four

INITIALIZING is initializing. Indicates the undo phase when the secondary database is being delivered and forced to write to the secondary database to keep up with the transaction logs required to undo LSN.

Note: when the database on the secondary replica is in the INITIALIZING state, forcing a failover to the secondary replica leaves the database in a state where the database can be started as the primary. The database will need to reconnect as a secondary database, or you will need to apply new log records from the log backup.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.