How to Shrink / Reduce the Size of the SQL Server Transaction Log

From WebWatchBotWiki
Jump to: navigation, search

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; however, keeping the recovery model "Simple" will help control the size of the transaction log. More information on database recovery models can be found here:
    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');
Note: 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 ('WebWatchBot_log');

Execute the following query to shrink the database:

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