Reduce The Size Of SQL Server Transaction Log

Introduction
To stop the nightmare of low disk space which cased by huge transaction log file. There three things to do. First truncate the inactive items in the transaction log. However it does not reduce the size of the log file. Secondly shrink the transaction log file. Finally, If possible set a fix maximum size of the transaction log file.

Truncate the inactive items in the transaction log
There are two ways to truncate the transaction log. Doing backup transaction log in the enterprise manager and truncate the transaction log by using T-SQL command.

Using SQL server enterprise manager to backup the transaction log will automatically truncate the inactive transactions after the backup. By doing this you need:
* Open the enterprise manager from the desktop shortcut or start menu.
* In the enterprise manager expend following items in the Console Root tree one by one: Microsoft SQL Servers, SQL Server Group, The database server which need perform backup task, Databases.
* Right click the database name and point to All Tasks, and then select Backup Database.
*Fill in the task name and description
* Click the radio button in front of the Transaction Log which in the backup section.
*In the Overwrite section choose Append to media or Overwrite existing media.
* Go to the Option tab make sure Remove inactive entries from transaction log has been selected.

If there are no enough space on the backup device or the backup of transaction log is not necessary. Executing truncate command in SQL Query Analyzer could be more straightforward.
* Open SQL Query Analyzer from start menu or executing the isqlw utility
* Select database which need truncate transaction log.
* Type in following T-SQL command:
use database name
BACKUP LOG WITH TRUNCATE_ONLY
* Press the execute button.

Shrinking the transaction log

Execute following command in the SQL Query Analyzer.

USE DatabaseName
DBCC SHRINKFILE(transaction log file name,desired shrink size in MB)


Fix the maximum size of the transaction log so you do not need worry about it again.

Open the enterprise manager from the desktop shortcut or start menu.
* In the enterprise manager expend following items in the Console Root tree one by one: Microsoft SQL Servers, SQL Server Group, The database server which need perform backup task, Databases.
* Right click the database name and point to Properties.
* Go to Transaction Log tab in the Database Properties window.
* Select Restrict file growth(MB): and fill in the size.