Let's consider a SQL Server AlwaysOn cluster with two nodes in asynchronous replication.
Is there any way to calculate how much data was lost after forced failover?

I mean in terms of time, to be able to know "I lost 1 hour of data or 1 minute". I have considered checking LSNs but I don't know how to translate them into datetime.

2 Answers 2

  1. Go to SSMS
  2. Object Explorer
  3. Expand Always On High Availability
  4. Expand Availability Groups
  5. Right click your AG
  6. Select Show Dashboard

In the dashboard, above the list of AG databases and their status, there is Add/Remove Columns button

Pick Estimated Data Loss (time) and add this column to dashboard
You will be able to see the estimated data loss time for each DB

You can check this dashboard right before you are going to do forced failover, to get the idea of how much data can be lost

AG Estimated Data Loss


You can create a "canary" table which contains only one row with one datetime column and update this column on scheduled basis (every minute, for example). This way you can check this table on secondary replica (even when primary one is already unavailable) and be sure that it is synchronized at least till this timestamp (actully you may lose less data because some transactions may be synced after the last "canary" table update job was executed).

  • Would you need to have the same table in each database participating in the AG, as some would possibly be further behind than others due to transaction volume?
    – BCM
    Commented Nov 10, 2020 at 16:10
  • 1
    @BCM yes, you would need to create the table in each database Commented Nov 10, 2020 at 16:12

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.