How to empty a SQL Server database transaction log file

I have 1GB databases that grow several GB log files.  How do you safely clear those logs?  Well, probably the best practice includes backing them up.  That was overkill for me, and sifting through all the documentation and forums (eventually) led me to this simple pair of statements that do the trick immediately:

backup log [dbname] with truncate_only
go
DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY)
go

Update: Mathias added a comment that deserves calling out:

Warning: Clearing the transaction log with TRUNCATE_ONLY will break your backup chain needed for FULL recovery. A FULL database backup should be executes right after truncating the logs. More info.

Related Posts

Are warning-free builds really a good thing?

I’m a big fan of pipelines that fail for warnings as well as errors. Such a policy keeps repos clean, current, less buggy, and even more agile….

Library.Template: A git repo template for .NET development

It is amazing what the .NET SDK can enable you to build with so little code. But very soon you need to add a cloud build pipeline…

Should I merge or rebase in git?

A lot has been said about whether folks should rebase, squash or merge into their git repos. It has almost gotten to the level of religious arguments…

All about RSA key formats

I’ve spent the past few weeks building up the PCLCrypto library which targets .NET Framework, Windows Store (WinRT), Windows Phone (WP8), Silverlight (SL), Xamarin.Android (XA) and Xamarin.iOS…

Moving on… DotNetOpenAuth in search for new project leaders

Disclosure and disclaimer: I am a software engineer at Microsoft, but the following post (just like all other posts on this blog) is my own and in…

How to get meld working with git on Windows

Inspired by these instructions, I followed these steps: Install Python 2.6 Install PyGTK All-in-one installer Install meld Then you need to configure git to be able to…

This Post Has 25 Comments

  1. with 400 users and a report dept that suddenly started “trying things out”, I had a log file almost as large as the VM I was running in.

    Thanks for your blog entry ! You’re a lifesaver !

  2. thanks a Lot Man, I am really happy, trying to email the database since long, but fail due to file size.

    Muhammad Sharjeel Ahsan

  3. Does this work on SQL 2008?
    I tried it and got the error:

    Msg 155, Level 15, State 1, Line 1
    'truncate_only' is not a recognized BACKUP option.

  4. hi i tried this in sql server 2008 enterpirse
    following error got
    Msg 155, Level 15, State 1, Line 2
    'truncate_only' is not a recognized BACKUP option.

Comments are closed.