Skip to content

Instantly share code, notes, and snippets.

@sirsql
Created December 30, 2015 18:41
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save sirsql/d6c4128ffa35d3198034 to your computer and use it in GitHub Desktop.
Save sirsql/d6c4128ffa35d3198034 to your computer and use it in GitHub Desktop.
Just provides very basic info around how your AG is doing. Run on primary to see sending stats, run on secondary to see receiving stats (and see how far behind things are)
SELECT db_name(database_id ), database_id as di,
datediff(minute, last_sent_time, last_received_time) AS SendRec,
datediff(minute, last_received_time, last_redone_time) AS RecRedo,
--datediff(minute, last_sent_time, last_redone_time) as MinsRedo,
case when redo_queue_size = 0 then 0
when redo_rate = 0 then 0
else cast(redo_queue_size / (redo_rate*1.0) as numeric(12,3)) end as MinToRedo,
case when log_send_queue_size = 0 then 0
when log_send_rate = 0 then 0
else cast(log_send_queue_size / (log_send_rate*1.0) as numeric(12,3))
end AS SecToLogSend,
log_send_queue_size as SendQueue,
log_send_rate ,
redo_queue_size ,
redo_rate , synchronization_state_desc ,
synchronization_health_desc ,
database_state_desc ,
is_suspended ,
suspend_reason_desc ,
last_sent_time ,
last_received_time ,
last_hardened_time ,
last_redone_time ,
end_of_log_lsn ,
last_commit_lsn ,
last_commit_time FROM sys.dm_hadr_database_replica_states
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment