SQL Server : Log File Shrink

Question

We are running SCCM 2012 r2 Reporting services and the SQL Server log file has got out of hand due to the backup not being set up properly and I'd like to confirm something before I continue.

I have set up our backup solution to do full backups of the DB and an incremental that is set to truncate the log on a regular schedule now but from what I've read, shrinking your log file is a bad idea due to performance issues, but it seems like this is the sort of case where it seems appropriate as we're sitting around 220GB for the log file.

Firstly, should I definitely be attempting to shrink the log file and what is the best way to do it? Is it simply by using DBCC SHRINKFILE?

Secondly, I believe I should be setting a reasonable limit on the log file size after shrinking but I'm unsure how to also come up with this limit. Is it just a size I'm happy with it taking up in-between truncation backups or is there a best practice.

As you may tell I'm not that familiar with SQL Server so I apologise if I've worded anything incorrectly. Thanks in advance.

Answer 1

In addition to your Full and Incremental/Differential backups you should be doing a backup on the transaction log in a reasonable frequency to mark all locations in the log file with committed entries as available for reuse instead of doing a truncation on the log file. With a correctly sized log file the transaction log backups will help to keep your log file from growing continuously and might only grow if it a larger volume of changes come in between backups windows then you predicted for.

Now on to log file shrinking. Shrinking your log file is not necessarily bad it will just take a bit of planning to do it correctly. (Make sure you are doing this in a maintenance window with a full backup before you begin and if at all possible without any other users connected.) You can use the DBCC shrink command on it to get it down to 1MB but right after you should regrow the log file back to a size that is appropriate for the rate of change that is happening in your database plus a free space buffer to handle larger than normal amounts of data changes. Also growing the log file in specific consistent increments will create evenly sized VLFs that can help with log performance. For example after you shrink it down to 1MB you regrow the log file in 1GB increments until you hit 5GB in size.

Kimberly Tripp has a couple of good articles explaining VLFs and transaction log throughput that might be helpful to read over. Do take note of her warning about a bug in growing the log file by 4GB.

Here is another VLF reference from Brent Ozar that has some more details and also references Kimberly's posts.

It's worth noting, Thanks @BradC, that shrinking and regrowing your log file should only have to be done once or only if your log file grows causing unevenly sized VLFs. But if you set your auto growth size on the log file to the specific size increments that use used to originally grow it back from 1MB you shouldn't see uneven VLFs even after it auto grows.

Answer 2

It sounds like your database is in full recovery mode, but you are not running transaction log backups. That's not a compatible combination.

You have 2 choices:

1. Leave it in full recovery mode, and take (and keep) regular transaction log backups in addition to your full and differential backups.
2. Switch it to simple recovery mode.

Note this is a business decision, not a technical one.

Full recovery mode is only necessary if you need "point-in-time" recovery, the ability to rewind to any arbitrary point between full/differential backups if something goes wrong.

If the database is in simple recovery mode and something bad happened (corruption, someone deletes a table, etc), then you'd have to go back to the latest full or differential backup, and you could lose more recent changes made to the database. But for certain kinds of databases (reporting/processing databases, development/test/QA environments, etc), this is acceptable.

Once you've decided which mode you need to support and made the appropriate changes, your tran log shouldn't be yo-yo-ing up and down in size. Use a query like the following to see how much internal space is actually being used in your file:

SELECT DB_NAME() as dbname, type_desc, name as logical_name, 
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
FROM sys.database_files WITH (NOLOCK)
ORDER BY type, file_id;

Between tran log backups you'll see the "used" space in the log file grow, and it should drop down when the log backup runs. The total file size should stay the same. I would suggest shrink the log file down to the largest "used" value you see plus, say, 20%, and leaving it with a growth setting of 2GB or 4GB, so it can grow a bit if it needs to.

Answer 3

The primary performance issue with shrinking the log is the fact that (outside of extraordinary situations, like yours) it will need to grow again. It can actually take longer to grow the log file than the data file (new space in the data file can basically be marked as "free", where new log file space has to actually be written over with zeros). Plus, if you shrink the log file down and then use that space for something else, then when the log file does need to grow, it may not be able to.

DBCC SHRINKFILE is what I would use. If in doubt, yuou can set up what you want via SSMS (right-click the DB; choose Tasks -> Shrink -> Shrink Files), and have it generate the script for you. Basically, you just need to specify the file to shrink and the target size.

Log file shrinks usually happen quickly. However, you won't get an error if the file didn't shrink. SQL does not rearrange the log file to free up space at the end of the file, andd will only drop space from the end. Issue the command multiple times, if needed; this can generate enough activity to shift the current activity point in the log, and move it past the end. If all else fails, try again later.

You can limit the log file growth, but you do risk that chance that you'll need more space than you've allowed at some point, and operations will fail because the log is full. If you've got free space, I would probably just check on the log periodically, to make sure all looks good.