Shrinking the Transaction Log in SQL Server

Shrinking the Transaction Log in SQL Server 2000 / 2005 with DBCC SHRINKFILE

SQL Server 2000

1.	Run this code: DBCC SHRINKFILE(pubs_log, 2) NOTE: If the target size is not reached, proceed to the next step.
2.	Run this code if you want to truncate the transaction log and not keep a backup of the transaction log. Truncate_only invalidates your transaction log backup sequence. Take a full backup of your database after you perform backup log with truncate_only: BACKUP LOG pubs WITH TRUNCATE_ONLY -or- Run this code if you want to keep a backup of your transaction log and keep your transaction log backup sequence intact. See SQL Server Books Online topic "BACKUP" for more information: BACKUP LOG pubs TO pubslogbackup
3.	Run this code: DBCC SHRINKFILE(pubs_log,2) The transaction log has now been shrunk to the target size. Source : http://support.microsoft.com/kb/907511/en-us

SQL Server 2005

1. Back up the transaction log file to make most of the active virtual log files inactive. Therefore, the inactive virtual log files can be removed in a later step. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.

BACKUP LOG TO DISK = ''

Note In this statement, is a placeholder for the name of the database that you are backing up. In this statement, is a placeholder for the full path of the backup file.
For example, run the following Transact-SQL statement.

BACKUP LOG TestDB TO DISK='C:\TestDB1.bak'

2. Shrink the transaction log file. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.

DBCC SHRINKFILE (, ) WITH NO_INFOMSGS

Note In this statement, is a placeholder for the name of the transaction log file. In this statement, is a placeholder for the target size that you want the transaction log file to be. The target size must be reasonable. For example, you cannot shrink the transaction log file to a size that is less than 2 virtual log files.

3. If the DBCC SHRINKFILE statement does not shrink the transaction log file to the target size, run the BACKUP LOG statement that is mentioned in step 1 to make more of the virtual log files inactive.

4. Run the DBCC SHRINKFILE statement that is mentioned in step 2. After this operation, the transaction log file should be similar to the target size.

Source : http://support.microsoft.com/kb/907511/en-us