DBCC ShrinkFile not shrinking the log file size

Question

I have a DB that has a log file(40GB) that is greater than the data file size(32GB). The log file has 99% of free space. When I execute DBCC shrinkfile statement, my log file size will not shrink. Database is set to Full recovery model. Full backups are being taken daily at midnight. Log backups are being taken hourly. Here is what I did to shrink:

1. Queried LOG_REUSE_WAIT_DESC to see that it is waiting for LOG_BACKUP
2. Queried DBCC Opentran ---> No active transactions
3. Took a manual log backup, then shrunk it. --- > Did not shrink
4. Set the DB to Simple, then shrunk it. ----> Did not shrink

Did some research online to see what other things I can try and most of the articles say to do the steps above ^. Is there a reason why log files are not shrinking? Thans in advance.

Answer 1

Hi @SQLLover21 ,

Please run DBCC LOGINFO, and share us the output. This will give you information about your virtual logs inside your transaction log. The primary thing to look at here is the Status column. Since this file is written sequentially and then looped back to the beginning, you want to take a look at where the value of "2" is in the output. This will tell you what portions of the log are in use and which are not in use Status = 0.

If your transaction log that is not growing, and you’re taking regular log backups, but the log_reuse_wait_desc remains LOG_BACKUP, this is because zero VLFs were cleared when the previous log backup was performed.

How can that happen?

Imagine a database where there’s very little insert/update/delete/DDL activity, so in between your regular log backups there are only a few log records generated, and they’re all in the same VLF. The next log backup runs, backing up those few log records, but it can’t clear the current VLF, so can’t clear log_reuse_wait_desc. As soon as there are enough changes in the database that the current VLF fills up and the next VLF is activated, the next log backup should be able to clear the previous VLF and then the log_reuse_wait_desc will revert to NOTHING. Until the next log backup occurs and isn’t able to clear the current VLF, in which case it will go back to LOG_BACKUP again.

So LOG_BACKUP really means “either you need to take a log backup, or the log records that were backed up were all in the current VLF and so it could not be cleared.”

Refer to the blog Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?

> The log file has 99% of free space

Did you using DBCC SQLPERF(logspace) to check this?

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Answer 2

That "2" would have to be at the beginning, so that you can truncate what comes after it. You would have to generate log records to move it around. That is perform DML operations in it.

However, there is a shortcut if you can accept to take the database offline for a short while:

CREATE DATABASE yourdb_snap ON (NAME = 'yourdb', FILENAME = '<somepath>')
AS SNAPSHOT OF yourdb

RESTORE DATABASE yourdb FROM DATABASE_SNAPSHOT = 'yourdb_snap'

That is, you create a database snapshot and revert from that snapshot.

The transaction log will now only be 0.5 MB, so size it to the size you need.

This step is somewhat dubious, since it breaks the log chain, but you have already done that by setting the database to simple recovery.

Normally, you don't want to break the log chain, because there could be corruption hiding in the database, and the last good backup may be from three weeks ago, so you would have to restore that backup and apply transaction logs.