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.
1 Answer
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.
select log_reuse_wait_desc from sys.databases where name='db_name'--your database name