Saturday, July 08, 2006

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.

25 comments:

  1. Michael Irwin10:37 AM

    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 !

    ReplyDelete
  2. Anonymous9:35 AM

    Great code. Thanks. You saved me 50 GB of my server's hard drive.

    ReplyDelete
  3. Arvin11:11 PM

    Thank for sharing this SQL. I've been looking for this for a very long time.

    ReplyDelete
  4. Sandeep K N9:06 AM

    thanx a lot dude

    ReplyDelete
  5. Anonymous5:06 PM

    It's my turn to say : "Thanks, man!" :)

    ReplyDelete
  6. Sebastien2:02 AM

    awesome, finally someone with the answer i'm looking for...

    ReplyDelete
  7. g1ga12:38 PM

    so simple so great! bro you saved my life! :D

    ReplyDelete
  8. Göran4:25 AM

    Wonderful, I'm so happy :)

    ReplyDelete
  9. ansblog4:11 AM

    GOD BLESS YOU, DUDE !

    It's so nice to see the .ldf file just 1.25 MB ! :) thx a lot

    ReplyDelete
  10. sharjeel10:47 AM

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

    Muhammad Sharjeel Ahsan

    ReplyDelete
  11. PLAST1C8:57 AM

    Thanks a lot!!!

    ReplyDelete
  12. PLAST1C8:59 AM

    thanks a lot man

    ReplyDelete
  13. Pakoros9:44 PM

    ํYou solve my problem.
    Thank you very much.

    ReplyDelete
  14. marty8:24 AM

    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.

    ReplyDelete
  15. Rani Shoura8:33 PM

    Thanks a lot !!!!

    ReplyDelete
  16. Waltee9:46 AM

    Thanks Much - This info helped greatly

    ReplyDelete
  17. Ibrahim9:57 AM

    I Like :)

    ReplyDelete
  18. Lordtoro8:50 PM

    Thank you :)

    ReplyDelete
  19. Matthias8:29 AM

    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.

    Source: http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/9997e75e-f126-4901-9379-de540a708ec9#352c6a1d-1866-4737-ada4-7eeff91cbcda

    ReplyDelete
  20. Shyam Masala6:13 PM

    Thank you!

    ReplyDelete
  21. Unknown5:01 AM

    You've saved the world !
    Thanks !

    PS. This does not work on 2008

    ReplyDelete
  22. Jos3:25 AM

    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.

    ReplyDelete
    Replies
    1. Unknown2:58 PM

      mine too 'truncate_only' is not a recognized BACKUP option.

      Delete
  23. Johnson Welch5:24 AM

    Interesting post… good info, This is one example of why I get frustrated with SQL Server. I have found another helpful post see here: http://www.sqlserverlogexplorer.com/how-to-clear-transaction-with-dbcc-shrinkfile/

    ReplyDelete
  24. Unknown8:30 AM

    This is Awsome!!!! Thank you so much! You safe my HDD!!

    ReplyDelete

Subscribe to: Post Comments (Atom)