Mike's Blog

SQL Server Tip - Shrinking a SQL Server log

If you want to shrink the size of the transaction log, which you think has become much larger than you would like, there are a two options.

Detach and Attach
First I will explain a trick for quickly remove the log file by detaching and automatically recreate a new log file by attaching the database. Remember that this would not work if the database has more than one log file. When you detach the database using sp_detach_db or using the UI in SQL Server Management Studio, SQL Server will know that the database was cleanly shutdown. This will ensure that the log file does not need to be available to attach the database again, so you could delete it. When you attach the database again, SQL Server will create a new log file for you, which will be of the minimum size.

The following are the steps to do:

1. Detach the database using SP_DETACH_DB procedure (Ensure no processes are using the database files.) 
2. Delete the log file
3. Attach the database again using SP_ATTACH_DB procedure

or

1. Open SQL Server Management Studio
2. Open content sensitive menu of the database
3. Choose Tasks > Detach
4. Delete the log file
   
5. Open content sensitive menu of the server
6. Choose Attach
7. Select the the database .mdf
   
   
   Note: Make sure you remove the the log file from the database details, as you can see in the upper screenshot.

Since you are detaching and attaching the database within the same server you will not have the problem of broken logins.

Backup and Shrink file
This option is not a trick, but the official way to shrink the transaction log file. Before you will use it, I will like to mention this option not always result in the way you want it. I haven't found out why, but sometimes the size of the log file will not result in the size you requested.

The following are the steps to do:

1. Backup the the transaction log using BACKUP LOG database WITH TRUNCATE_ONLY. Where database is the name of the database.
2. Shrink the file using DBCC SHRINKFILE(database_log, 50). Where 50 is the preferred size in MB of the log file.

or

1. Open SQL Server Management Studio
2. Open content sensitive menu of the database
3. Choose Tasks > Back Up
4. Choose Back up type - Transaction Log
   
5. Open content sensitive menu of the database
6. Choose Tasks > Shrink > Files
7. Choose File type - Log
8. Choose Shrink Action (it's good to leave some empty space. e.g. 50 MB)
   
   

Summary
For shrinking the log consisting of one or more log files, you can find very good documentation by searching the article kb256650 (for SQL 7.0) and article kb272318 (for SQL 2000) in Microsoft Knowledge Base. Another interesting article is How to stop the transaction log of a SQL Server database from growing unexpectedly, which applies to SQL 2000 and 2005.