|
|
|
KB: How to Reduce the Size of the SQL Server Transaction Log
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
- Open "Microsoft SQL Server Management Studio"
- Switch the database recovery mode to "Simple" (This can be changed back after this procedure).
- Locate your database, and right click on it
- Select "Properties"
- On the left pane, select "Options"
- On the right pane, select "Simple" from the "Recovery Model" dropdown list.
- Click OK
- Create a new query: Click "New Query" on the toolbar.
- Type in the query:
CHECKPOINT;
DBCC SHRINKFILE ('databasename_log');
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.
- If the transaction log fails to shrink, you will need to backup the entire database first, then
try again.
|
|
|
