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 REBUILD
s 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;