Note: for the purposes of this article I am assuming a full recovery model (which is the default).
I have occasionally had database transaction logs grow out of control on SQL Server 2005, e.g. 100M of data and 5G of transaction log. (Specifically, SharePoint configuration databases have done this to me a number of times.) This can affect performance and will most certainly affect backups, aside from obviously consuming inordinate amounts of disk space. You might be thinking “don’t autogrow the transaction log”, but that does not really solve the problem, it simply shortens the amount of time before you have to deal with it. :)
A transaction log has entries for each change you make to the database since the last backup. Internally, the transaction log file is divided into sections called virtual log files. When the current virtual log file fills up and a new one is needed, SQL Server 2005 will allocate the virtual log file that is closest to the beginning of the physical file(s). Under normal circumstances, following a database or transaction log backup, most of the virtual log files will be considered inactive, especially those at the end of the transaction log file. In such cases, if you tell SQL Server to shrink the files you get most of the unused disk space freed. What sometimes happens, though, is that a transaction log entry is still active in a virtual log file closer to the end of the physical file. In such cases, you may only see a small amount of file space in use because there are only one or a few virtual log files in use, but because of the location of the virtual log file within the physical file you cannot shrink the physical file.
To resolve this, first open SQL Server Management Studio to your database instance, and under Management open the Activity monitor. Look at the applications holding locks and shut them down or otherwise prevent them from maintaining a connection so that you have nothing actively using the database but your own login. Then open a new query and execute the following statements, one at a time.
BACKUP LOG database_name TO DISK = ‘X:\some\backup\folder\database.bak‘
DBCC SHRINKFILE (logical_database_log_name, 10) WITH NO_INFOMSGS
If you do that and the file does not shrink, or does not shrink very much, run the statements again; you may want a new backup filename. For some odd reason, it sometimes takes two runs to get a result. You read that right; even Microsoft’s own knowledgebase says to do the same (article 907511).
Another approach that has sometimes worked for me when the above does not:
- Assign a second log file
- Migrate all of the log to the 2nd log file
- Shrink the transaction log files
- Migrate all of the 2nd log back to the first
- Remove the second log
You can do all of this through SQL Server Management Studio.
Update: Please see my article Shrinking SQL Server 2005 log files – part deux for yet another approach.
Also, if you are not aware of it, a good practice is to create a maintenance plan that backs up the transaction logs on a periodic basis. I usually set up daily maintenance plans that backup the transaction logs, shrink the files, do index rebuilds and update statistics, then schedule these to run at a time least likely to interfere with anything but be done before users start their day.