1

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:

enter image description here

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: enter image description here

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.

3

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.

| improve this answer | |
1

This status apparently means that before I shrink the transaction log file, I must do a backup, however I just did a backup?

You have to do a LOG BACKUP, or switch the database to SIMPLE recovery mode.

| improve this answer | |
  • I did do a log backup, that's why I'm confused. I did a log backup, and then I went to shrink the log file (since doing the log backup truncated the majority of the log file). Even though I did a log backup, I'm still getting blocked by the LOG_BACKUP status. – Scriptonaut Mar 11 at 0:48
  • 1
    You may need to do more the 1 log backup in order to get the active VLF at the beginning of the log file and be able to shrink it. – Dominique Boucher Mar 11 at 12:28
  • @DominiqueBoucher You were right, I created a job to do a log backup every minute, shut off all the active connections to the db, and let that run 10 times or so. After that I was able to shrink the log file. I guess it just needed multiple backups in a row, not something I'd have expected. Thanks. – Scriptonaut Mar 12 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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