SQL Server AlwaysOn-how far behind is Secondary November 4th, 2013

Vinod Kumar

If you ever wondered why I wrote a simple post like SQL Server AlwaysOn-Find my Primary, this post is really for you. There are a lot of times customers ask me how much time is my secondary behind my primary? Such a simple question can be answered easily using some good DMVs. Here is the script I use extensively when working with AlwaysOn customers to identify what is doing behind the scene’s in their setup.

SELECT AGS.name                       AS AGGroupName,
AR.replica_server_name         AS InstanceName,
Db_name(DRS.database_id)       AS DBName,
AR.availability_mode_desc      AS SyncMode,
DRS.synchronization_state_desc AS SyncState,
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

Remember to run the above query on your Primary server. I have placed the output in two images for your reference.

The above columns give us an indication to our setup. What is the availability group, what are the servers, their roles, DB involved, their modes and what is their current state.

The second image which is above is the critical and the most important columns include when was the last_hardened_time and the last_redone_time. This shows how far away is my secondary. Also if the end_of_log_lsn of my Primary is different from the last_redone_lsn you know our secondary is behind my primary. For the above setup I have stopped to data sync to get this effect. Having said that, you get the drift how powerful this DMV can be.

This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).

Tags: , , , , , , , , ,

This entry was posted on Monday, November 4th, 2013 at 09:44 and is filed under Book, Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

5 Responses to “SQL Server AlwaysOn-how far behind is Secondary”

  1. Vijay says:

    Thank you Sir

  2. Pavithra says:

    I have set up always on. My secondary server is now lagging behind by 17 hours. The log harden time is in sync with the commit time on the primary. The secondary server commit last happened 17 hours ago.

    I have verified the blocks, logs etc and wasn’t able to find the root cause.

    One observation is that the load on my primary server has drastically increased.
    the diff backup is about 140 Gigs in a days time. That I see may be the reason. Any inputs will be greatly appreciated.

  3. Gayathri says:


    My Secondary server is far behind the primary. The lag is close to 12 hours now. The last commit time on the secondary is observe was 12 hours back.

    What could be the reason for this? Any idea?

  4. Phaneendra says:

    Hi Vinod,

    I would request you to get Indian version of this book printed if possible. Sure you understand the reason behind my request :)


    • Vinod Kumar says:

      We are having challenges in getting an Indian version of Print book. Having said that, the best is to use the Kindle version for across the world. Somethings are not in our capacity Phaneendra.

      Thanks again for dropping a line.

Leave a Reply