How to Reduce the Size of the SQL Server Transaction Log

From WebWatchBotWiki

Over time, with the use of SQL Server, the database transaction log file can grow quite large. This is actually normal and by design; however, it is not always the most desired behavior.

Using DBCC queries through SQL Server tools, you can force a resize of the database transaction log.

The following steps are confirmed to work on SQL Server 2005

1. Open "Microsoft SQL Server Management Studio"
2. Switch the database recovery mode to "Simple" (This can be changed back after this procedure).
   1. Locate your database, and right click on it
   2. Select "Properties"
   3. On the left pane, select "Options"
   4. On the right pane, select "Simple" from the "Recovery Model" dropdown list.
   5. Click OK
3. Create a new query: Click "New Query" on the toolbar.
4. Execute in the query:

     use databasename; 
     CHECKPOINT;
     DBCC SHRINKFILE ('databasename_log');

Execute the following query to shrink the database:

     use master;
     CHECKPOINT;
     DBCC SHRINKDATABASE('WebWatchBotCGI');

databasename_log is the actual filename of the transaction log for your selected database on
the filesystem that you want to shink. No path needed.
For WebWatchBot, it is WebWatchBot_log, e.g. DBCC SHRINKFILE ('databasename_log');

• If the transaction log fails to shrink, you will need to backup the entire database first, then try again.