SELECT r.replica_server_name
AS Replica, DB_name(rs.database_id) 'database_name',
rs.is_primary_replica IsPrimary,
rs.last_received_lsn,
rs.last_hardened_lsn,
rs.last_redone_lsn,
rs.end_of_log_lsn,
rs.last_commit_lsn
FROM sys.availability_replicas r
INNER JOIN sys.dm_hadr_database_replica_states rs ON r.replica_id = rs.replica_id
ORDER BY replica;
select log_reuse_wait_desc from sys.databases where name like 'DB_Name'
SELECT ar.replica_server_name as Replica,
adc.database_name,
drs.is_local,
drs.is_primary_replica,
drs.synchronization_state_desc,
drs.is_commit_participant,
drs.synchronization_health_desc,
drs.recovery_lsn
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id
AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id
AND drs.replica_id = ar.replica_id
ORDER BY Replica;
Secondary replica is lagging behind the Primary replica
SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server,
db_name(dr_state.database_id) AS database_name,
is_ag_replica_local = CASE
WHEN ar_state.is_local = 1 THEN N'LOCAL'
ELSE 'REMOTE'
END,
ag_replica_role = CASE
WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'
ELSE ar_state.role_desc
END,
dr_state.last_hardened_lsn, dr_state.last_hardened_time,
datediff(s,last_hardened_time, getdate()) AS 'seconds behind primary'
FROM (( sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
ON ag.group_id = ar.group_id)
JOIN sys.dm_hadr_availability_replica_states AS ar_state
ON ar.replica_id = ar_state.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state
ON ag.group_id = dr_state.group_id AND dr_state.replica_id = ar_state.replica_id
Status of Replica
SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server,
db_name(dr_state.database_id) AS database_name,
is_ag_replica_local = CASE
WHEN ar_state.is_local = 1 THEN N'LOCAL'
ELSE 'REMOTE'
END,
ag_replica_role = CASE
WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'
ELSE ar_state.role_desc
END,
ar_state.connected_state_desc, ar.availability_mode_desc, dr_state.synchronization_state_desc
FROM (( sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state
ON ar.replica_id = ar_state.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state
ON ag.group_id = dr_state.group_id AND dr_state.replica_id = ar_state.replica_id
Database Replica Log remaining for undo
SELECT [instance_name], [object_name], [counter_name], [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Database Replica%' AND [counter_name] = 'Log remaining for undo'
Monitor Log Send Queue Size
SELECT DB_Name(database_id) as 'DB_Name', log_send_queue_size, log_send_rate, redo_queue_size , redo_rate FROM sys.dm_hadr_database_replica_states
--WHERE is_local = 0;
log_send_queue_size: The amount of transaction log (in KB) that needs to be sent to the secondary replica.
log_send_rate: The rate at which the transaction log is being sent to the secondary replica (in KB per second).
redo_queue_size: The amount of transaction log (in KB) that needs to be redone on the secondary replica.
redo_rate: The rate at which the transaction log is being redone on the secondary replica (in KB per second).
