3

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!

Improve this question
4
  • Interesting problem indeed. How are you currently measuring / monitoring the change in the Transaction Log file size?
    – J.D.
    Dec 13, 2020 at 22:10
  • dbcc sqlperf(logspace) - Log Space Used (%) is increasing.
    – Amam
    Dec 13, 2020 at 22:12
  • It's normal for the % to increase (as the pre-allocated free space is re-used) but does it not go back to ~0% after the transaction log backups finish?...do you only see the "Log Size" column continue to grow every time you check it?
    – J.D.
    Dec 13, 2020 at 22:23
  • 1
    "Log Size" is already pre-allocated to a bigger size... Log Space Used (%) continues to grow and is not reducing insipite of the tlog backups. Once Log Space Used (%) will become 100% used then "Log Size" will start increasing but I m not at the point yet...have a few hours before it hits that state.
    – Amam
    Dec 13, 2020 at 22:27

1 Answer 1

Reset to default
0

Restarting the secondary replica server and taking another fullbackup and then subsequent Tlog backup fixed the issue.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.