2

SQL14 is the Primary server, SQL16 is the Secondary Replica, and they're setup using synchronous commit availability mode:

AlwaysOn AG Dashboard

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): SQL Server Error Log 1

SQL Server Error Log 2

SQL Server Error Log 3

Not sure if there's any clues in there?

DBCC OPENTRAN on the Secondary returns the following: DBCC OPENTRAN on Secondary

Extended events session to track waits on the Secondary: Extended Events Session on Secondary

1

1 Answer 1

5

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'>>YOUR AG NAME HERE<<', 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;
1
  • After further research I was able to fairly conclusively attribute the issue I experienced due to our index maintenance job. I didn't realize initially (though I should've realized sooner) that the transaction log is essentially the root of how the Redo Queue works. Our long running maintenance job from this morning (actually started last night) ran for 12 hours straight before completing, blowing up our transaction log faster than the Secondary Replica could handle so it was playing catch up for a few hours this morning.
    – J.D.
    Nov 19, 2019 at 23:13

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.