It’s true that you don’t want to continually shrink and grow the log file for a few different reasons including fragmentation and incurring the overhead of growing files during a request. However, if you have decided that your transaction log is in fact too large and needs to be shrunk, here is one way to go about it.
First, you need to determine how large your transaction log should be. The easiest way is to look at the size of your transaction log backups over a span of time. Use this script as a base and tweak as necessary.
SELECT
backup_start_date,
database_name,
backup_size
FROM
msdb..backupset
WHERE type='L'
ORDER BY
backup_start_date DESC
What is the largest normal log backup? That will tell you roughly how large your transaction log should be as long as your not running into exceptional circumstances on a regular basis.
If you are running into exceptional circumstances on a regular basis, you might need to change the way you are managing the db or you might have to accept the fact that these circumstances are not as exceptional as you once thought and consider them to be within the limits of expected operation.
Once you have the size:
- Run a tran log backup.
- Disable all backup jobs and file operations that might run while you do the shrink.
- Shrink the log file to the appropraite size.
- Change the size of the log file in database properties (or through T-SQL).
- Re-enable the jobs/operations you previously disabled.
This should give you correctly sized logs.
You run the log backup before you shrink to make sure that you don’t have active virtual log files (VLF) that will prevent the shrink from actually reducing the file size.
File shrinks and transaction log backups are not allowed to run concurrently so you have to disable anything that would conflict with the shrink.
You change the size of the log file to make sure you have the least amount of VLFs. More VLFs can reduce performance although from what I’ve read this is more of an issue in 2000 than 2005. I have not personally seen any issues due to misconfigured log files yet though so I can’t say for sure.