Why does last_sent_time column of sys.dm_hadr_database_replica_states always show NULL

Chinni 21

I always say NULL for column last_sent_time of sys.dm_hadr_database_replica_states on primary replica. It shows correct value for secondary replica.

Why is this not showing correct data for primary?

3 answers

Ronen Ariely 15,096 Reputation points

2022-04-01T18:31:26+00:00

Hi,

Why is this not showing correct data for primary?

It is showing correct data for primary which is null :-)

Did you check what this column is?

When querying the primary replica, last_sent_time is reported for each secondary replica database row. Time when the last log block was sent.

it is by definition mean to report for each secondary replica and not for primary

----------

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin
Please sign in to rate this answer.
Chinni 21 Reputation points

2022-04-01T18:47:30.65+00:00

I am not sure if that is what it is meant, why can't this be meaning "primary reporting the time of last log back sent from primary to each secondary"?

The description also says "Time when the last log block was sent."

It must be sent from primary right? so it should the time that primary sends the logs?

Also we have another column in the same dmv which is last_received_time

"When querying a secondary replica, last_received_time is reported for the local secondary replica database row."

last_received_time is showing the time received at the secondary. So this column is addressing the secondary side of the things

Ronen Ariely 15,096 Reputation points

2022-04-02T21:35:25.757+00:00

Hi,

why can't this be meaning "primary reporting the time of last log back sent from primary to each secondary"?

Maybe because this is not the meaning so it does not say so...

If you have multiple secondary then who told that the last_sent_time is the same to all these replicas?!? This value relevant per secondary and not per primary. It report for each secondary replica database row

last_received_time is showing the time received at the secondary. So this column is addressing the secondary side of the things

Yes, this column addressing the secondary side and provide totally different information. The time something sent is not always the same time as it received

Real story: I am waiting for a package from US which was sent to several people. The time that was sent to each of us was different last_sent_time) and I am still waiting for my package while others got it already so we each have different last_received_time and some did not even received it :-)

Ronen Ariely 15,096 Reputation points

2022-04-04T14:27:47.963+00:00

Off-topic, the package I was waiting for has arrives :-)

A huge thanks to the Microsoft Power Platforms team
Sign in to comment
CathyJi-MSFT 21,091 Reputation points Microsoft Vendor

2022-04-04T05:30:44.557+00:00

Hi @Chinni ,

last_sent_time column of sys.dm_hadr_database_replica_states show NULL, this is a correct value for primary replica.

last_sent_time and last_received_time are all reported for each secondary replicas in SQL AG. Below is a result in my environment, I have one SQL AG has three replicas.

In addition, please refer to this blog.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Chinni 21 Reputation points

2022-04-04T19:31:35.537+00:00

I understand the columns last_sent_tim, last_received_time much better now. Thanks for the replies. But I still can't understand that there is nothing to monitor the log sent information (in terms of time) on Primary directly.

Also to your question

"If you have multiple secondary then who told that the last_sent_time is the same to all these replicas?!? "

Since we have one row per secondary, this should not be a problem right? it can show the send times per secondary.

My main requirement is that I want to monitor if there is any* progress at from the Primary in terms of sending logs to secondary. I can't use the queue size on primary as this can increase or decrease.

In Database Mirroring we have the counter 'Log Bytes Sent/sec' which is not null on Principal and it updates (cumulative) every time a log is sent to Mirror. I do not see any such counter for alwayson.

Mirroring: https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-database-mirroring-object?view=sql-server-ver15
Please sign in to rate this answer.
Ronen Ariely 15,096 Reputation points

2022-04-06T20:51:21.49+00:00

Hi :-)

I understand the columns last_sent_tim, last_received_time much better now.

Glad we could help a bit in this :-)

But I still can't understand that there is nothing to monitor the log sent information (in terms of time) on Primary directly.

The time that the Primary send is different from one secondary to the other, so the information come in the records of the secondary. What do you expect to have differently? Do you want to get multiple duplicate rows for the primary in order to have a different value for the sending time!?? Do you want to get the time of the first sending or the last sending? you cannot have multiple values in one column in the a table :-)

Since we have one row per secondary, this should not be a problem right? it can show the send times per secondary.

True and it does. It is in the rows related to the secondary and not in the rows of the primary

This is exactly the meaning last_sent_time in the secondary information.

Ronen Ariely 15,096 Reputation points

2022-04-06T20:51:32.087+00:00

My main requirement is that I want to monitor if there is any* progress at from the Primary in terms of sending logs to secondary.

This is good question

I think that your best solution is to get the information from each of the secondary

In Database Mirroring we have the counter 'Log Bytes Sent/sec' which is not null on Principal and it updates (cumulative) every time a log is sent to Mirror. I do not see any such counter for alwayson.

Database Mirroring is a very very and deprecated solution. Time move and we have new solutions to get some task while other task simply not needed anymore :-)

You can get this information using Extended Events which were not exists when data mirroring invented

Chinni 21 Reputation points

2022-04-15T18:30:30.08+00:00

Thank you for the responses
Sign in to comment