How To Shrink or Clear Out Large SQL Server Log Files

First, let me preface this article for all you SQL Server DBAs reading this, in no way am I recommending that the following procedure be used in a production (or development depending on your restoration policy) environment unless you know what you are doing, and are aware of the repercussions to clearing the transaction logs. If not, feel free to read Aaron Bertrand's post on the topic. The main purpose of this article is for those that are in the same situation I often find myself, quickly configured databases in a development environment that do NOT require transactions NOR restoration to any other point other than the last FULL database backup (*.bak).

Now, on to the article...

While migrating our development database server environment, I was reminded how huge SQL Server transaction log files can get. Of course in a development environment, transaction logs are may be irrelevant, so prior to migrating it is always best to truncate the log files as not to move GIGs of log files. These are the ways I am aware of to do such a thing.

First, perform a FULL DB BACKUP of the database (yes, seems redundant, but you will be glad if something fails and you don't lose a days worth of work.)

Update: I've been getting so much flak from people in the community using truncate_only, I have decided to scratch it out. Continue to the recommended method below.

You can use the following SQL command to backup the log file while truncating the existing logs (note: this is deprecated in 2008):

backup log [dbname] with truncate_only
go

dbcc shrinkdatabase ([dbname], 10, truncateonly)
go

OR

You can do the same through SQL Server Management Studio:

1. Right click the database to be "shrunk" | Properties
2. Under Select a page click Options
3. Change Recovery Model to Simple | Ok
4. Right click the database | Tasks | Shrink | Database | Ok
5. Make sure to set the recovery mode back to Full (or not, depending on your needs)
6. Done!

Obviously if you are in a production environment, you probably want to actually backup your logs before you truncate.