Transaction Log files are not safe to delete

Published On: 2009-12-10By:

All to often I see people online saying that they detach a database then delete the log file to shrink it.  This is a very dangerous operation which can leave you with a corrupt database that you can’t do anything with.

While you can normally attach a database without the log file, it doesn’t always work.  Sometimes SQL Server won’t be able to recover from this and create a new log file and you’l have lost the database.

If your database is growing and has gotten very large, then you’ll want to change the recorvery mode from FULL to SIMPLE.  This will help keep the transaction log file small, and is perfectly safe to do.  The only thing you will loose is the ability to do point in time restores of the transaction log.  However point in time restores are only available if you backup the transaction log (which would have prevented the log from growing out of control to begin with).

The reason that many people do this, is that the name of the file is misleading.  Normally logs are very safe to delete, however the transaction log isn’t your normal log file.  A transaction log keeps a record of every change to the database, along with the before and after values.  All changes are written to the transaction log before they are commited to the database itself so that if the server fails the database is left in a consistant state.

If your log file has simply gotten huge, and you need to make it smaller, you can use the DBCC SHRINKFILE command to shrink the log file to a smaller size.  This shouldn’t be done on a regular basis.  If you are doing this on a regular basis leave the file large it’s at the size it needs to be or change the database recovery to SIMPLE.


Contact the Author | Contact DCAC

4 responses to “Transaction Log files are not safe to delete”

  1. Ramam says:

    Hey Denny,
    I’ve got a 15GB DB and I’m using weekly full backups with 15m tlog backups. I keep four weeks worth of fulls and tlogs as we are still seeing a lot of base level application code changes and are being extra cautious in terms of point-in-time recovery ability.
    I was planning on adding in daily differential backups so that I can toss my old tlogs mostly to reduce the number of files to manage but also to speed up recovery events. Given that differentials are really a rolling up of tlogs since the last full back up I am wondering if:
    1 Can I do point-in-time recovery using: the newest full backup prior to the time I want + the newest differential backup prior to the time I want + tlogs as necessary from that differential backup to the actual point-in time.
    2 This is probably answered by the first question’s answer but for clarity sake: is it ok to delete the tlogs that are older than differentials?

  2. Mrdenny says:

    Differential backups aren’t just a rollup of the transaction logs. They are actually a copy of each record which has had an internal flag changed.

    1. Yes, provided that the point in time is after the differential was taken.
    2. Yes.

  3. Ramam says:

    I realize that I wasn’t clear in my question but you answered me anyway. I specifically wanted to know if point-in-time recovery can be done for periods where there are differential backups but not tlogs and the answer is no.
    Thanks again – great blog!

  4. Mrdenny says:

    Sorry, Yesterday was kind of a ruff day, I thought I got all your questions answered. But at least you got the info you needed. 🙂


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link