I have read these articles few times. I do monitor potential data loss today using some of the methods described in these articles.

  1. Measuring Availability Group synchronization lag by Derik Hammer
  2. Monitor Performance for AlwaysOn Availability Groups
  3. Monitoring Availability Group Replica Synchronization by Jonathan Kehayias

Reading these articles and the description of dynamic management views and performance counters, I do not see any way to measure the exact time for potential data loss if Primary goes down now.

I did inquire about using log sequence number (LSN) values from sys.dm_hadr_database_replica_states. Found out only certain LSN's has a timestamp. Plus, continuously querying the timestamp of those particular LSN is probably not a practical solution.

Do you know how to get the exact time for potential data loss at any given time?

A hypothetical solution will be finding the oldest LSN's timestamp that has not been copied to the secondary.

1 Answer 1


I've occassionally created specific tables for this purpose, sometimes called "tracer" or "canary" tables. It has one column which is a datetime. A job runs on the source system inserting the current time. This runs at whatever granularity you need - minutes apart was sufficient for me. Read the max value from the secondary & that's how far behind you are.

  • Thank you. I have done that too and blogged about it. That included 'log copy'+'log replay'. I am not concerned about log replay because that is not going to cause me data loss. I want to exactly measure how far behind I am on the copy. Commented May 21, 2021 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.