Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save nanoDBA/c0e1771a625f4b34daba330690e9f04f to your computer and use it in GitHub Desktop.
Save nanoDBA/c0e1771a625f4b34daba330690e9f04f to your computer and use it in GitHub Desktop.
How far behind is Secondary in AlwaysOn replica ? ( LAG )
--How far behind is Secondary in AlwaysOn replica ? ( LAG )
--http://blogs.extremeexperts.com/2013/11/04/sql-server-alwayson-how-far-behind-is-secondary/
--"There are a lot of times customers ask me how much time is my secondary behind my primary?
--Such a simple question can be answered easily using some good DMVs."
SELECT AGS.name AS AGGroupName,
AGL.dns_name AS Listener_dns_name,
AGL.port AS Listener_port,
AGL.ip_configuration_string_from_cluster AS Cluster_IP_addresses,
AR.replica_server_name AS InstanceName,
HARS.role_desc,
Db_name(DRS.database_id) AS DBName,
DRS.database_id,
AR.availability_mode_desc AS SyncMode,
DRS.synchronization_state_desc AS SyncState,
DATEDIFF(MINUTE, DRS.last_redone_time, DRS.last_hardened_time ) AS DELAY_min,
DATEDIFF(SECOND, DRS.last_redone_time, DRS.last_hardened_time) AS DELAY_sec,
DATEDIFF(MILLISECOND, DRS.last_redone_time, DRS.last_hardened_time) AS DELAY_ms,
DRS.redo_queue_size,
DRS.log_send_queue_size,
DRS.last_hardened_time,
DRS.last_redone_time,
DRS.last_hardened_lsn,
DRS.end_of_log_lsn,
DRS.last_redone_lsn
FROM sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR
ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS
ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
LEFT JOIN sys.availability_group_listeners AGL
on AGL.group_id = AGS.group_id
--WHERE HARS.role_desc = 'PRIMARY'
--WHERE Db_name(DRS.database_id)= 'SomeDbName'
ORDER BY AGS.name,
AR.replica_server_name,
Db_name(DRS.database_id)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment