Always On Query

We can use this query to check when the last log has been committed in Secondary replica in Alwasy on.

SELECT AGS.NAME AS AGGroupName
    ,AR.replica_server_name AS InstanceName
    ,HARS.role_desc
    ,DRS.synchronization_state_desc AS SyncState
    ,DRS.last_hardened_time
    ,DRS.last_redone_time
    ,((DRS.log_send_queue_size)/8)/1024 QueueSize_MB
,datediff(MINUTE, last_redone_time, last_hardened_time) as Latency_Minutes
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

---One more query for Depending on which replica was having difficulty communicating, you should query both the primary and secondary for the local replica:

select r.replica_server_name, r.endpoint_url,
rs.connected_state_desc, rs.last_connect_error_description, 
rs.last_connect_error_number, rs.last_connect_error_timestamp 
from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r
on rs.replica_id=r.replica_id

where rs.is_local=1
----One more query to check the log info
--Run this on the primary server WHEN the secondary are up to see how long recovery will take
--Note that the recovery is per database so you will need to account for that.

select ag.name as AGName
--, ag.group_id
,ar.replica_server_name
,sd.name
,ar.availability_mode
,drs.is_primary_replica
,drs.synchronization_state
,drs.log_send_queue_size  as log_send_queue_size  --the queue on the primary ONLY AFTER THE SECONDARY IS BACK UP
,drs.log_send_rate as log_send_rate_kb_sec --rate log info is being sent to the secondary
,drs.redo_rate as secondary_redo_rate_kb_sec --rate redo occurs on the secondary
,drs.redo_queue_size as secondary_redo_queue_size_kb --redo queue size on the secondary
,drs.redo_queue_size / replace(drs.redo_rate,0,1) as redo_queue_in_seconds --time for that database to redo all CURRENTLY in the queue
,drs.log_send_queue_size / replace(drs.log_send_rate,0,1) as TimeToSendOverInSeconds--how long to send.*****Generally a reliable piece as to how long recovery will take.
,drs.last_hardened_time --hardened on the secondary replica
--,drs.last_redone_time
from sys.availability_groups ag
join sys.availability_replicas ar on
ag.group_id = ar.group_id
join sys.dm_hadr_availability_replica_states ars
on ar.group_id = ars.group_id and ar.replica_id = ars.replica_id
join sys.dm_hadr_database_replica_states drs
on ars.group_id = drs.group_id and ars.replica_id = drs.replica_id
join sys.databases sd
on drs.database_id = sd.database_id
where
is_primary_replica =0--switch to 0 or 1 if needed
--and
--last_hardened_time > dateadd(minute,-500,getdate())
--and
-- (drs.log_send_queue_size > 512 or drs.redo_queue_size > 512)
order by name, replica_server_name