secondary_lag_seconds RRS feed

  • Question

  • For always-on , if the secondary replica is set the sync-commit, the secondary_lag_seconds (from DMV sys.dm_hadr_database_replica_states )  is always be 0 ?????

    and this number is the different between the "last_commit_time" of primary replica and secondary replica ?

    Monday, August 19, 2019 5:54 AM

All replies

  • This field is very clear in your screenshot. It is the time when the secondary replica becomes the primary replica. No failover has occurred. Of course, it is 0.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" 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

    Tuesday, August 20, 2019 7:07 AM
  • ""It is the time when the secondary replica becomes the primary replica. " <<<????

    The number of seconds that the secondary is "BEHIND" the primary replica ?

    behind from what ? how it is compared ??

    Wednesday, August 21, 2019 1:45 AM
  • anyone has the idea ?
    Thursday, August 22, 2019 3:17 PM
  • Yes, it is possible that the secondary can be quite far behind the primary. What is guaranteed with synchronous is that log blocks are sent from the primary to the secondary immediately before the transaction is committed on the primary.

    Then the redo log will replay the transaction on the databases on the secondary. If there is a delay - the log blocks can be stored in the transaction log on the primary. All of these factors contribute to latency.  By looing at the log_send and redo queues you can tell where the bottleneck is.

    It can be possible that there will be schema locks held on the secondary which will block the redo thread which can add to the redo latency.

    Thursday, August 22, 2019 3:26 PM
  • so the "secondary_lag_seconds" means the time difference between the log_harden complete and log_redo complete in secondary replica ????

    Am I right ?

    Friday, August 23, 2019 2:35 AM