It’s 9PM and Someone Just Called About Failover
“The secondary is falling behind.”
Before you touch anything, you need to know:
- Is the replica actually connected?
- How far behind is the redo queue?
- Which databases are synchronised vs synchronising?
Guessing here is dangerous. A failover to a stale secondary means data loss.
This query tells you exactly where you stand.
What This Query Does
It joins across four DMVs:
sys.availability_groups— the AG namessys.availability_replicas— replica config (sync mode, failover mode)sys.dm_hadr_availability_replica_states— live replica healthsys.dm_hadr_database_replica_states— per-database sync state and queue depths
The columns that matter most in a crisis:
| Column | What It Tells You |
|---|---|
Sync Health | HEALTHY / NOT_HEALTHY / PARTIALLY_HEALTHY |
Log Send Queue KB | How much log hasn’t left the primary yet |
Redo Queue KB | How much log the secondary hasn’t applied yet |
Last Commit Time | Last confirmed commit on the secondary |
If Redo Queue KB is climbing, the secondary is falling behind.
If Connected is DISCONNECTED, you have a bigger problem.
Availability Group Health Script
Always On Replica Health.sql
Loading…
Gareth Winterman