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.
This is indicative of two possible items (or both).
- The system (storage, memory, cpu) isn't fast enough or have enough bandwidth to redo the log blocks.
- REDO may be blocked by a user thread if it's a readable secondary.
I have tried in GUI for resume data and do syncup but it's failed giving error.
Was it not synchronized? If not, that's a different problem. Pausing and resuming will potentially only make it worse, depending on the root cause.
Do we have any code for apply Redo pending data on secondary replica databases manually.
No, there isn't a button to press to tell the REDO to run. It runs. Constantly. All the time.
It doesn't run when it's blocked and it runs very slowly when it doesn't have the hardware to run properly (primary may be more powerful than secondary, etc.).
If you look at the secondary, there should be an AlwaysOn Health extended events session running. Opening the XEL file in management studio (SSMS) will give you what has been captured. Look for any REDO BLOCKED messages. If there aren't any, run a perfmon on the system to check on IO latency and throughput, memory, and cpu utilization.