How to Shrink SQL Server Transaction Logs

http://help.fogcreek.com/8686/how-to-shrink-sql-server-transaction-logs

Here’s a quick screencast that demonstrates how to change the recovery model for your database and shrink the database logs using SQL Server Management Studio:

 

---

Back up your database!

Launch SQL Server Management Studio.

Open up a query window associated with the database with the large transaction log. (Right-click on the DB and choose new query.)

Get the logical name of the transaction log file. (Right-click on the DB, select Properties, then in the Files screen, grab the Logical Name… probably ends in something like _Log.)

Execute the following, substituting with the appropriate logical name of the database log file, no quotes needed:

DBCC SHRINKFILE(<log_file_name_Log>)
BACKUP LOG <database> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<log_file_name_Log>)

Afterwards, perform a full backup of the database.

The file should shrink to a ridiculously small shadow of its former self.

Edit: A little more info from a customer.

The ‘proper’ thing to do these days
is to put the database into ‘simple
recovery’ and then to shrink the log.

A few commands that I think might do
it:

ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
DBCC SHRINKFILE(<log_file_name_Log>) 
ALTER DATABASE [mydatabase] SET RECOVERY FULL

Some notes on SQL Server backups:

• transaction logs have to be backed up in order for them to ‘truncate’. If they are not backed up regularly, they will eventually fill the disk
• a full backup alone will not do the trick – that does not touch the transaction log
• transaction logs must be backed up via ‘BACKUP LOG’
• if the dba does not require transaction log backups, it is advisable to move to Simple Recovery

A little more info here:

http://technet.microsoft.com/en-us/library/ms189085.aspx