none
@EstimatedDataLoss

    Question

  • Hi

    What exactly is the calculation logic/code behind the function/expression - @EstimatedDataLoss

    Helpsays its the number of seconds of transactions that have accumulated in the log send queue on the primary replica

    However, my sysAdmin insisted its the difference between the Last commit time between the primary & secondary.

    Rgds

    Gauri

    Tuesday, September 12, 2017 5:13 AM

All replies

  • Hi Gauri888,

    Estimate Data Loss indicates the time difference of the last transaction log record in the primary replica and secondary replica. If the primary replica fails, all transaction log records within the time window will be lost.

    Best Regards,

    Teige


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Tuesday, September 12, 2017 7:18 AM
  • Thanks Teige

    But what I am confused is - How does the system compute it? What variables are considered - Log send queue or Last Commit method (as given in msdn) Coz different people give different versions to the same - Some say that its the difference between the last commit time on Primary & secondary , while some say its the accumulation in the log send queue/gnrtn rate...

    I am actually looking out for the system code I think in computing this attribute! Would you give some more insight?

    Thanks & Best Regards

    Gauri

    Tuesday, September 12, 2017 8:04 AM
  • Hi Gauri888,

     

    Could you please tell me if what you mentioned is the @EstimatedDataLoss of Estimating Potential Data Loss (RPO)? If it is, it equals log⁡_send_queue/log⁡ generation rate 

    I suggest you referring to this article: https://msdn.microsoft.com/en-us/library/dn135338(v=sql.110).aspx

     

    Best Regards,

    Teige

     


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Tuesday, September 12, 2017 8:57 AM
  • Yes...

    I am referring to the same article... and in the same one, it is mentioned that alternatively we can even use the Last_Commit_time for calculating RPO.

    In my client place, they informed me initially that they are using Last_commit _time for calculating RPO & they even walkthrough one live instance on the dashboard where we could see the difference in commit time was populated in "estimated data loss"in the dashboard.

    However, I am still confused, since there is a function/property  "@estimateddataloss" which states number of seconds of transactions that have accumulated in the log send queue on the primary replica.

    Hence I am keen to know the code behind "@Estimateddataloss" 

    Thanks so much for your continued responses!

    Gauri

    Tuesday, September 12, 2017 9:08 AM
  • Hi Gauri888,

     

    Please refer to the following code, the value of @estimateddataloss is the difference between the Last commit time between the primary & secondary.

     

    WITH DR_CTE ( replica_server_name, database_name, last_commit_time)

    AS

    (

    select ar.replica_server_name, database_name, rs.last_commit_time

    from master.sys.dm_hadr_database_replica_states rs

    inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id

    inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id

    where replica_server_name != @@servername

    )

    select ar.replica_server_name, dcs.database_name, rs.last_commit_time, DR_CTE.last_commit_time 'DR_commit_time', datediff(ss, DR_CTE.last_commit_time, rs.last_commit_time) 'lag_in_seconds'

    from master.sys.dm_hadr_database_replica_states rs

    inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id

    inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id

    inner join DR_CTE on DR_CTE.database_name = dcs.database_name

    where ar.replica_server_name = @@servername

    order by lag_in_seconds desc

     

    Best Regards,

    Teige

     


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Monday, September 18, 2017 8:03 AM