question

narendba avatar image
narendba asked

Checking for Alwayson manual sync process for redo queue data

Hi, I have seen frequently few databases are having synchronization issue with secondaries and monitored the same and found redo data queue size is around in 2MB in secondary replicas. I have tried in GUI for resume data and do syncup but it's failed giving error. Do we have any code for apply Redo pending data on secondary replica databases manually.
sqlserver2012alwayson
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you post the error? Sometimes error messages contain vital clues as to the underlying problem.
0 Likes 0 ·
also, does the redo queue size fluctuate or does it only go up? Just wondering if redo is not happening at all or if it has trouble keeping up during periods of high data churn. and, just to clarify, only *some* of the databases in the availability group have such large redo queues? are these the busiest databases and do redo queues on other databases ever catch your notice for size changes? it might be helpful to track the redo queue size for all the databases and see if they follow similar trends, just at smaller magnitudes. does the secondary have the same performance capabilities as the primary server (same disk performance, memory and cpu capacity, etc)? it's also worth noting that the redo queue *may be* single threaded, so it can easily fall behind a busy multi-threaded workload on the primary - https://msdn.microsoft.com/en-us/library/ms187465.aspx
0 Likes 0 ·

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
This is the command to resume availability group replication for an individual database: ALTER DATABASE [database_name] SET HADR RESUME; Once this is set, there is not a separate command to actually force the replicated transactions through the redo queue. The redo rate is limited by system capacity and, as noted in the comments, the redo queue may only get one thread per database (1 thread per 4 CPUs on systems with more than 5 CPUs - [ https://msdn.microsoft.com/en-us/library/ms187465.aspx][1]) so an identically sized secondary can still fall behind a busy multi-threaded workload on a primary. It's possible that 2mb is just your high-water mark in this catch-up process. [1]: https://msdn.microsoft.com/en-us/library/ms187465.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.