0

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.

Scriptonaut
  • 137
  • 6
  • Based on your question, I'm assuming you're not using log backups for recovery. If that's the case then I would suggest setting the Recovery Model to Simple for this database to prevent this issue in the future (and any other databases that you're not performing log backups for). – joeqwerty Mar 10 '20 at 02:32
  • @joeqwerty We are doing data backups, and I want to change it to do full backups, both logs and data. That's why I want to shrink the log file, so I can move to a setup where we do transaction log backups for recovery. – Scriptonaut Mar 10 '20 at 19:27

0 Answers0