What's the cause for an AlwaysOn AG Secondary Replica to have a high Redo Queue Size and Estimated Recovery Time with a good Redo Rate? SQL14 is the Primary server, SQL16 is the Secondary Replica, and they're setup using synchronous commit availability mode:



As of yesterday it appeared that data stopped synchronizing from the primary to the replica. This morning we Suspended the Availability Database and then Resumed it. That seemed to kick the synchronization again as I see new data coming through now but the Redo Queue Size and Estimated Recovery Time in the dashboard are still big and are continuing to grow. 

What things can I check / do to fix this?

Additional Information:
Server Version: SQL Server 2016 Enterprise - SP1 (on both primary and secondary)

Also, we had some long running index re-organize / re-build jobs fail on the primary earlier in the morning. (That was about 4 hours ago but could that still be potentially a contributor to this issue now?)

I noticed the following server logs on the secondary (from oldest to newest):






Not sure if there's any clues in there?

DBCC OPENTRAN on the Secondary returns the following:


Extended events session to track waits on the Secondary:
So a number of things here... (e.g. this will be a long answer and may go through a number of iterations):

First and foremost did this AG experience a failover?  If not, then checking the ERRORLOGs on the Primary node is where I suggest you start your troubleshooting process; if a failover did occur, check the ERRORLOGs on the node formally known as the Primary to see what happened.  You can use the clunky UI within SSMS or you can use the undocumented extended procedure xp_readerrorlog, which is my recommendation as it's just quicker to filter out noise this way.  For instance, I'd suggest starting out looking for any references to the AG name like the following and digging from there:

xp_readerrorlog 0, 1, N'&gt;&gt;YOUR AG NAME HERE&lt;&lt;', NULL, '2019-11-18 12:00:00.000', '2019-11-19 15:00:00.000', NULL, NULL


Check the link above for syntax surrounding the command.

Based on what you find, expand the search checking your Windows Event Logs (eventvwr.msc), Cluster Logs (cluadmin.msc), Application logs, etc. using error times as a point of reference if necessary.  I highly suspect these logs will identify what caused the synchronization to stop, be it a cluster-related issue, a hiccup due to maintenance, etc.  Based on that, I suggest posting a new question if you are having a hard time interpreting the results.

Second, running Index Maintenance in an AG will cause AG sync delays.  There's no getting around it, regardless the version of SQL Server (SQL 2019 included) though the new versions tend to recover quicker.  If you feel you must run Index Maintenance and you have the ability to take the application offline, I suggest your run offline Index Maintenance.  Execute this as offline REBUILDs with MAXDOP defined, as offline index rebuild operations don't cause as severe of delays within your AG.  Obviously this approach will cause an outage, so it's not something to do lightly.  I do support an environment where we do this as neglecting index maintenance causes unnecessary growth (and in a multi TB system that causes other problems)

To put it plainly, a common misconception in the SQL Server community is that Index Maintenance is super critical to the performance of a database.  This generally just isn't the case as Index fragmentation has only minimal impact on execution plan behavior. Only when average white space per page approaches egregious levels, does fragmentation start to matter. Honestly, in most cases Index maintenance is basically a very expensive stats update operation, meaning be on top of Stats Updates which don't generally cause blocking and also don't back up AGs.

Finally, if you want to see how far behind secondaries are and get an estimate of how long it'll take them to catch up, give this query a try which is a variant of this code from Jonathan Kehayias:

SELECT ar.replica_server_name, 
       adc.database_name, 
       ag.name AS ag_name, 
       drs.is_local, 
       drs.synchronization_state_desc, 
       drs.synchronization_health_desc, 
       drs.last_redone_time, 
       drs.redo_queue_size, 
       drs.redo_rate, 
       (drs.redo_queue_size / drs.redo_rate) / 60.0 AS est_redo_completion_time_min,
       drs.last_commit_lsn, 
       drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc 
       ON drs.group_id = adc.group_id AND 
       drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
       ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar 
       ON drs.group_id = ar.group_id AND 
       drs.replica_id = ar.replica_id
ORDER BY 
       ag.name, 
       ar.replica_server_name, 
       adc.database_name;