Great post about shrinking SQL server 2005 log files.

Shrinking the File

Once you have identified your problem and have been able to truncate your log file,  you may need to shrink the file back to a manageable size.  You should avoid shrinking your files on a consistent basis as it can lead to fragmentation issues.  However, if you’ve performed a log truncation and need your log file to be smaller, you’re going to need to shrink your log file.  You can do it through management studio by right clicking the database, selecting All Tasks, Shrink, then choose Database or Files.  If I am using the Management Studio interface, I generally select Files and shrink only the log file.

This can also be done using TSQL.  The following query will find the name of my log file.  I’ll need this to pass to the DBCC SHRINKFILE command.

SELECT name FROM sys.database_files WHERE type_desc = 'LOG'

Once I have my log file name, I can use the DBCC command to shrink the file.  In the following command I try to shrink my log file down to 1GB.

DBCC SHRINKFILE ('SalesHistory_Log', 1000)

Also, make sure that your databases are NOT set to auto-shrink.  Databases that are shrank at continuous intervals can encounter real performance problems.

Help! My SQL Server Log File is too big!!! | TechRepublic.