Archive
Posts Tagged ‘AlwaysON Replica Latency’
Query to check AlwaysON Replica Latency for the LSNs (Log Sequence Numbers) by using DMVs
April 11, 2014
Leave a comment
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