AOAG Log Sync Analysis

 SELECT r.replica_server_name

            AS Replica, DB_name(rs.database_id) 'database_name',

       rs.is_primary_replica IsPrimary,

       rs.last_received_lsn,

       rs.last_hardened_lsn,

       rs.last_redone_lsn,

       rs.end_of_log_lsn,

       rs.last_commit_lsn

FROM sys.availability_replicas r

     INNER JOIN sys.dm_hadr_database_replica_states rs ON r.replica_id = rs.replica_id

ORDER BY replica;

select log_reuse_wait_desc from sys.databases where name like 'DB_Name'

SELECT   ar.replica_server_name as  Replica,

       adc.database_name,

       drs.is_local,

       drs.is_primary_replica,

       drs.synchronization_state_desc,

       drs.is_commit_participant,

       drs.synchronization_health_desc,

       drs.recovery_lsn

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 Replica;

Secondary replica is lagging behind the Primary replica 

SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server,

db_name(dr_state.database_id) AS database_name,

is_ag_replica_local = CASE

    WHEN ar_state.is_local = 1 THEN N'LOCAL'

    ELSE 'REMOTE'

    END,

ag_replica_role = CASE

    WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'

    ELSE ar_state.role_desc

    END,

dr_state.last_hardened_lsn, dr_state.last_hardened_time,

datediff(s,last_hardened_time, getdate()) AS 'seconds behind primary'

FROM (( sys.availability_groups AS ag

JOIN sys.availability_replicas AS ar

    ON ag.group_id = ar.group_id)

JOIN sys.dm_hadr_availability_replica_states AS ar_state

    ON ar.replica_id = ar_state.replica_id)

JOIN sys.dm_hadr_database_replica_states dr_state

    ON ag.group_id = dr_state.group_id AND dr_state.replica_id = ar_state.replica_id

 

 Status of Replica     

SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server,

db_name(dr_state.database_id) AS database_name,

is_ag_replica_local = CASE

    WHEN ar_state.is_local = 1 THEN N'LOCAL'

    ELSE 'REMOTE'

    END,

ag_replica_role = CASE

    WHEN ar_state.role_desc IS NULL THEN N'DISCONNECTED'

    ELSE ar_state.role_desc

    END,

ar_state.connected_state_desc, ar.availability_mode_desc, dr_state.synchronization_state_desc

FROM (( sys.availability_groups AS ag

JOIN sys.availability_replicas AS ar

    ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ar_state

    ON ar.replica_id = ar_state.replica_id)

JOIN sys.dm_hadr_database_replica_states dr_state

    ON ag.group_id = dr_state.group_id AND dr_state.replica_id = ar_state.replica_id

Database Replica Log remaining for undo

SELECT [instance_name], [object_name], [counter_name], [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Database Replica%' AND [counter_name] = 'Log remaining for undo'

Monitor Log Send Queue Size 

SELECT DB_Name(database_id) as 'DB_Name', log_send_queue_size, log_send_rate, redo_queue_size , redo_rate FROM sys.dm_hadr_database_replica_states 

--WHERE    is_local = 0;

log_send_queue_size: The amount of transaction log (in KB) that needs to be sent to the secondary replica.

log_send_rate: The rate at which the transaction log is being sent to the secondary replica (in KB per second).

redo_queue_size: The amount of transaction log (in KB) that needs to be redone on the secondary replica.

redo_rate: The rate at which the transaction log is being redone on the secondary replica (in KB per second).