Below is the script to check Always-ON replica states
SELECT
GETDATE() AS DateTimeCaptured
,r.replica_server_name
,s.role_desc
,d.synchronization_state_desc
,d.log_send_queue_size
,d.log_send_rate
,d.redo_queue_size
,d.redo_rate
,d.truncation_lsn
,d.last_sent_lsn
,d.last_sent_time
,d.last_received_lsn
,d.last_received_time
,d.last_hardened_lsn
,d.last_hardened_time
,d.last_redone_time
,d.last_commit_time
,d.low_water_mark_for_ghosts
FROM sys.availability_replicas r INNER JOIN
sys.dm_hadr_availability_replica_states s
ON r.replica_id=s.replica_id INNER JOIN
sys.dm_hadr_database_replica_states d
ON s.replica_id=d.replica_id
ORDER BY s.role_desc, r.replica_server_name DESC
Leave a Reply