On a heavily utilized system with lots of transaction, shrinking the log file may be difficult.
The normal procedure when shrinking the log file, is to first do a backup of the log. Then directly after that try to shrink the log file. But if there are active transactions writing to the log file, shrinking may not be possible, or at least not shrinking as much as expected.
Why shrink the log file?
Best practice is to never shrink any of SQL Servers files. That is certainly true for the data files. But in some circumstances, the log file may need shrinking. One cause for shrinking the log file is when a heavy and temporary batch process has expanded the log file beyond a size that normally is needed. Another example is when the number of virtual log files has grown beyond recommendations. This happens when the log file is expanded with a small amount each time, and when it has been expanded many times (several thousands). See my other blog post about that.
DBCC SHRINKFILE
To shrink the log file, the system command DBCC SHRINKFILE is used. The parameter target_size (MB) is used to specify how large the file should be after shrinking. For example, to shrink a file to 10 MB:
DBCC SHRINKFILE (file_name, 10);
To see the new size of the file, the following statement can be used:
select name, size*8/1024 from sys.database_files
If the new size isn’t as small as you expected, it’s because there are active portions of the log file that haven’t been “backuped” yet. So the first step should always be to do a log backup. But to make sure new transactions aren’t placed last in the log file before you have time to change the size, I recommend that you add a job step to the SQL Agent job that does the transaction log backup. This way the shrinking occurs immediately after the log backup and there is minimal time for new transactions to appear in the log file.
To add a job step isn’t a fool proof way to shrink the log, so you may need to try it a couple of times.
