Folks, stuck with a wierd transaction log truncation(reuse) problem in an AlwaysOn cluster and running out of options, the transaction log is not getting truncated and keeps growing. Only one database in the availability group, is refusing to truncate it's log file and rest of the databases in the AG are truncating upon tlog backup. One of the readonly (applicationintent) secondary replica was rebooted yesterday and this was the only new activity that happened on the AG cluster. Three node AG cluster. SQL Server 2016 SP1
I tried these steps
- 15 min Tlog backups working properly. Tlog backups are done on the secondary replica.
- select name , log_reuse_wait_desc from sys.databases --> NOTHING
- dbcc opentran --> No active open transactions.
- select database_id, log_send_queue_size, log_send_rate,redo_queue_size,redo_rate from sys.dm_hadr_database_replica_states --> redo_queue_size is 0 and log_send_queue_size is 0
- Verified that data movement from primary to both secondary databases is working fine and realtime
- There is no blocking on on any of the nodes in AG cluster.
- There are no active long running transactions on any of the nodes in AG cluster.
I'd really appreciate any input. Thanks!
