Created
April 22, 2019 17:37
-
-
Save nanoDBA/c0e1771a625f4b34daba330690e9f04f to your computer and use it in GitHub Desktop.
How far behind is Secondary in AlwaysOn replica ? ( LAG )
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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