Sql Server 2017 - Cannot shrink transaction log file, blocked by LOG_BACKUP status Last week my sql server 2017 instance (running on windows server 2016) ran out of space, filling the disk completely up with a 190GB transaction log file.

Luckily this was on a cloud instance, so I quickly unmounted the disk, grew it an additional 200GB or so, and then remounted and formatted the unused space.

However I still had this giant 190GB log file. Log backups weren't setup. So I took a manual backup of the transaction logs. I then ran a Disk Usage report, and thankfully it was showing that 95% of the transaction log space was unused:



However, the log file itself is still 190GB. After doing some research, people suggested that I shrink the log file, having just cleared most of the data out of it by doing a backup. So I tried shrinking it, and it processed for a few seconds, and then the shrink utility window closed, without any error. However, the log file remains at 190GB.

I queried the log_reuse_wait_desc for my database, and "LOG_BACKUP" was returned. This status apparently means that before I shrink the transaction log file, I must do a backup, however I just did a backup?

I am stuck at this point, I would like to have a smaller log transaction file (something like 10-20GB), and I would like to automatically backup the log, either regularly enough that it never exceeds 10-20GB, or automatically when the file hits the 10-20GB mark.

Does anyone know how to deal with the LOG_BACKUP status blocking my ability to shrink the file, besides simply running a backup (since I've already done that)?

Here are my dm_db_log_stats for the database:


You'll notice that my total vlf count is huge (1097), my active vls count is smaller (300), but I'm not sure what that means.
The ldf file is divided into Virtual Log Files (VLFs). The last used VLF sets the limit to how small the file can be shrunk. I.e., the file can only be shrunk from the end towards the beginning. So you apparently have a used VLF towards the end of the file. You need to get un-used VLFs at the end. 

What you do is you empty the log (BACKUP LOG if not in SIMPLE mode, else the CHECKPOINT command), and then shrink. And then you do that several times. That is the key, several times. At some point in time, you will have as many un-used VLFs at the end so the file will shrink to your liking.

And then set recovery model properly (based on if you do log backups or not).

You can investigate the VLF layout using DBCC LOGINFO.