SQL Server 2005

From: http://support.microsoft.com/kb/907511

Use SQL Server Management Studio for the following commands from a query window

BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'

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

DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

OR:

See Microsoft knowledgebase article at:

http://support.microsoft.com/kb/272318

You will need to execute the commands referenced in the article from the SQL console window. To open the console, open a CMD window. From the c: prompt enter:
osql -E

After a moment or two the console will return with a 1> prompt. You are now in the SQL console window.

Each of the commands referenced in the article must be followed with a GO command. You may need to begin with a USE command such as:
USE dbname

MORE INFORMATION

When DBCC SHRINKFILE is run, SQL Server 2000 shrinks the log file by removing as many virtual log files as it can to attempt to reach the target size. If the target file size is not reached, SQL Server places dummy log entries in the last virtual log file until the virtual log is filled and moves the head of the log to the beginning of the file. The following actions are then required to complete the shrinking of the transaction log:

1.	You must run a BACKUP LOG statement to free up space by removing the inactive portion of the log.
2.	You must run DBCC SHRINKFILE again with the desired target size until the log file shrinks to the target size.

The following example demonstrates this with the pubs database and attempts to shrink the pubs_log file to 2 MB:

Run this code:

DBCC SHRINKFILE(pubs_log, 2)

NOTE: If the target size is not reached, proceed to the next step.

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

Run this code:

DBCC SHRINKFILE(pubs_log,2)

The transaction log has now been shrunk to the target size.

SQL Shrinking the Transaction Log

MORE INFORMATION