Question about sys.dm_hadr_database_replica_states

• Jim_K

  SSCrazy

  Points: 2418

  More actions

  July 22, 2015 at 7:53 am

  #398783

  I ran into something that seems weird, and BOL doesn't seem to say what the meaning is. I wrote a quick query to check and see how the replication is doing in an AlwaysOn Availability Group with SQL 2012 SP2 CU5:

  SELECT DB_NAME(RS.database_id) AS Expr1, AR.replica_server_name, RS.is_local, RS.recovery_lsn, RS.truncation_lsn, RS.last_sent_lsn, RS.last_sent_time,

  RS.last_received_lsn, RS.last_received_time, RS.last_hardened_lsn, RS.last_hardened_time, RS.last_redone_lsn, RS.last_redone_time, RS.end_of_log_lsn,

  RS.last_commit_lsn, RS.last_commit_time

  FROM sys.dm_hadr_database_replica_states AS RS INNER JOIN sys.availability_replicas AS AR ON RS.replica_id = AR.replica_id

  The strange thing I noticed was that last_sent_lsn sometimes returns as "1" instead of the longer number I expected. Puzzled, I added a where clause to filter all of those out and only return the databases that had what I assumed should be there.

  SELECT DB_NAME(RS.database_id) AS Expr1, AR.replica_server_name, RS.is_local, RS.recovery_lsn, RS.truncation_lsn, RS.last_sent_lsn, RS.last_sent_time,

  RS.last_received_lsn, RS.last_received_time, RS.last_hardened_lsn, RS.last_hardened_time, RS.last_redone_lsn, RS.last_redone_time, RS.end_of_log_lsn,

  RS.last_commit_lsn, RS.last_commit_time

  FROM sys.dm_hadr_database_replica_states AS RS INNER JOIN sys.availability_replicas AS AR ON RS.replica_id = AR.replica_id

  WHERE EXISTS (SELECT 1

  FROM sys.dm_hadr_database_replica_states fRS

  WHERE fRS.database_id = RS.database_id AND (last_sent_lsn > 1))

  If you execute the query a couple of times over five minutes or so databases will appear in the returned set, and then not be there on the next run (because the last_sent_lsn = 1). Does the value of 1 in this field basically mean that there is nothing to replicate? BOL seems to hint at this as it says:

  wrote:

  So any LSN < the value of last_hardened_lsn is on disk. LSN that are >= to this value are not flushed.

  1 would probably always be less then the value in last_hardened_lsn.