Question

It's possible with some configurations of SQL server for the transaction logs to grow very large. This has even at times threatened disk space on some of our customers' servers! How do I use DBCC SHRINKFILE to shrink these logs down?

Answer 1

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: https://fogcreek.viewscreencasts.com/89b8525e6256817c5b0a2e22dfb1e481

---

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>)

Afterwords, 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

Answer 2

Here's an MSDN article for shrinking files in SQL Server 2008. Note that BACKUP LOG WITH TRUNCATE_ONLY has been discontinued in SQL Server 2008.

http://msdn.microsoft.com/en-us/library/ms190757.aspx

Answer 3

Yo can set the database in Simple mode if you don´t want to have a transaction log that grows and grows and grows.