question

KaziArifulHaq-9926 avatar image
0 Votes"
KaziArifulHaq-9926 asked ·

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

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-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Shashank-Singh avatar image
0 Votes"
Shashank-Singh answered ·
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered ·

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.


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.