Availability Groups and dm_hadr_database_replica_states dmv

• ganci.mark

  Hall of Fame

  Points: 3533

  More actions

  April 30, 2018 at 1:07 pm

  #328601

  Hello.
  I am trying to figure out what the difference is between the following columns  on

  sys.dm_hadr_database_replica_states   (SQL 2016 )

  last_sent_time
  Last_receive_time
  last_hardened_time
  Last_redo_time

  last_sent_time and last_receive_time seem to age out while last_hardened_time and last_redo_time seem to stay in line with changes being made on the primary and replication.
  What do the values in last_sent_time and last_received_time actually mean/represent?

  Thanks
  Mark G.

• Michael L John

  One Orange Chip

  Points: 27038

  More actions

  April 30, 2018 at 2:25 pm

  #1988701

  ganci.mark - Monday, April 30, 2018 1:07 PM

  Hello.
  I am trying to figure out what the difference is between the following columns  on

  sys.dm_hadr_database_replica_states   (SQL 2016 )

  last_sent_time
  Last_receive_time
  last_hardened_time
  Last_redo_time

  last_sent_time and last_receive_time seem to age out while last_hardened_time and last_redo_time seem to stay in line with changes being made on the primary and replication.
  What do the values in last_sent_time and last_received_time actually mean/represent?

  Thanks
  Mark G.

  Well, typing sys.dm_hadr_database_replica_states into a google search returned this link:
  https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql?view=sql-server-2017

  Which states:

  last_sent_time

  datetime

  Time when the last log block was sent.

  last_received_time

  datetime

  Time when the log block ID in last message received was read on the secondary replica.

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• ganci.mark

  Hall of Fame

  Points: 3533

  More actions

  May 1, 2018 at 2:37 pm

  #1988872

  Thanks Mike.
  I did get that far with the Microsoft documentation however  What I was looking for was a better explanation of what each value meant in relation to the replication process. 
  last_sent_time
  Last_receive_time
  last_hardened_time
  Last_redo_time
  How are these related to each other and  the data replication process.

  Real example: 
  last_sent_time =2018-05-01 16:29:46.050
  last_received_time  = 2018-05-01 16:29:46.050
  last_redone_time = 2018-05-01 16:29:36.563  
  last_hardened_time= 2018-05-01 16:29:38.040
  Does this mean the changes were sent/received at 16:29:46  and it took 10 seconds to "redo"(apply) them and 2 seconds to "commit"(harden) them?

  Thanks

  Mark G.