SQL Server log file continues to grow?

All we need is an easy explanation of the problem, so here it is.

I’m having an issue with a SQL Server log file which will not maintain a steady amount of space held on a HDD which it has been issued. The drive is ~50GB and the log file is growing to occupy this quite often.

The database is set to FULL recovery mode, with hourly differential backups. I understand that this should allow the log file to truncate and use space already allocated to it?

When checking the status of the log file using DBCC LOGINFO it returns that the status of most VLF’s within the files are set to ‘2’ which shows them as being active? We’ve 537 VLF’s in the log file. I don’t know why all these would be active at one point using 15GB of space?

I’ve checked the replication through DBCC OPENTRAN and it reports that there are no actively open transactions on the file.

Is there a method of maintaining this file I may of overlooked?

Is there anywhere I can go to determine exactly what the log file is doing?

Would replication interfere with truncating/shrinking the log file?

At present, it grows around 1.67GB per day. I’ve ~18 days before having to perform some emergency work on shrinking the file. I’d like it to stay below 25GB if possible so there is plenty of room to grow if this has a sudden need for space.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

No. A differential never truncates the log. Please consider your recovery needs and choose a recovery model that fits those requirements.

There’s still a widely held misconception that when properly in the
FULL or BULK_LOGGED recovery models that full or differential backups
can truncate the log. No. It NEVER happens.

http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/

I’d recommend easing up on the Differentials and start with scheduling t-log backups ever 15 minutes.

More information:

Once you get your t-logs backed up appropriately then you’ll want to leave the shrinking alone. Whatever it grows to (usually within that 15 minutes) is usually the growth profile of what it actually needs. To shrink the log file only to let it grow again will cause unnecessary overhead and may impact performance.

If you want to consolidate your VLFs then you need to find an outage window when you can shrink the log file and then grow it back to it’s preferred size using a standard increment (use growth increments of 8000MB)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply