SQL Server Always On avilability group sycn status/latency gap check

Kazi Ariful Haq 161 Reputation points
2021-03-03T13:55:59.287+00:00

Hi,

Does anyone have good query to check sycn status/latency gap between primary and secondary replica of Always ON availability group (2017 version)? Can we check the sync/latency gap by time?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,760 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shashank Singh 6,246 Reputation points
    2021-03-03T14:06:24.653+00:00
    0 comments
  2. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-03-04T06:18:56.477+00:00

    Hi KaziArifulHaq-9926,
    Please refer to this query from this article which might help: 

    SELECT   
	ar.replica_server_name,   
	adc.database_name,   
	ag.name AS ag_name,   
	drs.is_local,   
	drs.is_primary_replica,   
	drs.synchronization_state_desc,   
	drs.is_commit_participant,   
	drs.synchronization_health_desc,   
	drs.recovery_lsn,   
	drs.truncation_lsn,   
	drs.last_sent_lsn,   
	drs.last_sent_time,   
	drs.last_received_lsn,   
	drs.last_received_time,   
	drs.last_hardened_lsn,   
	drs.last_hardened_time,   
	drs.last_redone_lsn,   
	drs.last_redone_time,   
	drs.log_send_queue_size,   
	drs.log_send_rate,   
	drs.redo_queue_size,   
	drs.redo_rate,   
	drs.filestream_send_rate,   
	drs.end_of_log_lsn,   
	drs.last_commit_lsn,   
	drs.last_commit_time  
FROM sys.dm_hadr_database_replica_states AS drs  
INNER JOIN sys.availability_databases_cluster AS adc   
	ON drs.group_id = adc.group_id AND   
	drs.group_database_id = adc.group_database_id  
INNER JOIN sys.availability_groups AS ag  
	ON ag.group_id = drs.group_id  
INNER JOIN sys.availability_replicas AS ar   
	ON drs.group_id = ar.group_id AND   
	drs.replica_id = ar.replica_id  
ORDER BY   
	ag.name,   
	ar.replica_server_name,   
	adc.database_name;

    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments