Home
> SQL Tips > Query to check AlwaysON Replica Latency for the LSNs (Log Sequence Numbers) by using DMVs
Query to check AlwaysON Replica Latency for the LSNs (Log Sequence Numbers) by using DMVs
SELECT ag.NAME AS 'AG Name' ,ar.replica_server_name AS 'AG Replica Server' ,DB_NAME(drs.database_id) AS 'Database Name' ,CASE WHEN ars.is_local = 1 THEN N'LOCAL' ELSE 'REMOTE' END AS 'Is AG Replica Local' ,CASE WHEN ars.role_desc IS NULL THEN N'DISCONNECTED' ELSE ars.role_desc END AS 'AG Replica Role' ,ar.availability_mode_desc AS 'Sync Mode' ,drs.synchronization_state_desc AS SyncState ,agl.dns_name AS 'Listener Name' ,drs.last_hardened_lsn ,drs.last_hardened_time ,datediff(s, last_hardened_time, getdate()) / 60 AS 'Seconds Behind Primary' ,drs.last_commit_time FROM sys.availability_groups AS ag INNER JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id INNER JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id INNER JOIN sys.dm_hadr_database_replica_states drs ON ag.group_id = drs.group_id AND drs.replica_id = ars.replica_id INNER JOIN sys.availability_group_listeners AS agl ON agl.group_id = ars.group_id ORDER BY datediff(s, last_hardened_time, getdate()) DESC;
Categories: SQL Tips
AlwaysOn, AlwaysON Replica Latency
Comments (0)
Trackbacks (0)
Leave a comment
Trackback