0

The Redo Queue Size has been growing pretty big over the last couple of hours on my secondary replica's server (SQL16). There's nothing long running or heavy running that I've found over the last hour or so, so I'm not sure where this is coming from.

My index maintenance jobs ran last night which are pretty beefy and normally cause this "lag" in sync between the replicas but that usually clears up by the morning.

I log the Redo Queue Size and can see where it spiked through the night and then started coming down as per usual. The jobs finished hours ago, and the Redo Queue Size spiked to around 10 GB during the job and after the job finished came back down to around 1.5 GB, but now it's growing again even though nothing heavy has been running since.

Note: My secondary replica is setup as a synchronous replica.

Availability Group Dashboard

7
  • Is the secondary replica readable? Mar 13, 2020 at 13:40
  • 3
    I have seen this occur at times on our remote nodes. I think a thread(s) is getting hung and causing it to be slow. I restart SQL Server service on the secondary and then it clears out the redo queue. Seems to happen after issues with the network link. Mar 13, 2020 at 13:47
  • 2
    Check that you don't have locked processes on the secondary replica. It's possible a redo thread was blocked by some reading query on the secondary. Additionally have a look at this article. Mar 13, 2020 at 13:56
  • 1
    @J.D. What will happen is that replication to that node will be paused, and after the service is restarted, it recovers the DB, which requires emptying the redo queue (so to speak), and then replication will be resumed. If it would get corrupted or lose transactions, then you could never restart a secondary and nobody would be using AGs. Mar 13, 2020 at 14:44
  • 1
    When it's in the "Redo", it's already written to the log (on your secondary) and SQL is just redoing the transaction in order to get the data page updated. As you are in "synchronous" mode, the transaction is not commit on the primary until it is written to the log file of the secondaries. If you restart your SQL instance, it will replay the log. Mar 13, 2020 at 15:12

2 Answers 2

2

There is no way to "debug" this other than to open an incident with Microsoft for investigation. They may be able to get a series of process dumps or enable some more verbose logging to see if there is a hung thread, but you may have to leave it in the current condition for quite a while, so that is the downside.

Based on my experiences with the same symptoms, stopping and starting the SQL Server service on the secondary will resolve the issue (unless you have an underlying I/O issue that is causing it). It may take a while for the database to recover as it will have to play all of the transactions in the redo queue, and during this time, replication will be paused. You can track progress of database recovery in the error log, and once the database is recover, replication will resume.

1

Please look at this webpage:
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-performance-for-always-on-availability-groups?view=sql-server-ver15

There is different metrics to look at and it can help you understand what is causing your issue.

Acutally, the redo part is when SQL is applying the logs that were written to the ldf file of the secondary nod in order to update the data page in the mdf.

Check the "SQL Server:Database Replica > Redone Bytes/sec" perf counter to see the redo rate. If this number is large, it may just be that there is more activity on the primary node then normal. If that number is low, you may want to look for session that can hold locks on your data pages for example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.