A database in an availibility group on MS SQL 2014 with a decent log file which at some occasions has been shrinked because of some major jobs filling it up. Usually don't shrink logfiles but as there's a lot of other databases that don't grow at the same time it has been done instead of extending disk, it's a VMWare environment. Now the logfile has grown a lot again, filling up the disk. The log disk was extended, transaction logs backed up and trying to shrink the file about 10-20 GB but there's still only 200 MB free space in the logfile.

  • What does following return select log_reuse_wait_desc from sys.databases where name='db_name'--your database name – Shanky Apr 6 at 9:13
  • Thanks, I checked on that again and I been looking at the wrong line in sys.databases, it says AVAILABILITY_REPLICA so I got some problem with replication or something. – StiffBoard Apr 6 at 10:11
  • No, that does not necessarily means problem. This means the primary is waiting for an acknowledgement from secondary to mark transaction in trn log as committed so that they can be truncated post transaction log backup. This is common in AG. Just make sure the bandwidth between primary and secondary is not loaded. – Shanky Apr 6 at 10:31

Explanation to this problem was that replication halted on one database in Availability group. Transaction log backup was successful but log file could not be emptied because replication to secondary server was paused. I did not notice this looking at the databases initially. And didn't notice that log_reuse_wait_desc was showing AVAILABILITY_REPLICA on the primary server (looking at the wrong line in result). But when checking again after Shankys reply I discovered that and Availability group Dashboard showed that replication had halted. Resumed replication, backed up log again and then I was able to shrink logfile to a more accurate size.

| improve this answer | |

Your Answer

