Advertisements
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;
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: