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.
Best Answer
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.