What to do about massive log file

  • One of our old SQL Databases has a log file that has grown to almost 100GB. I have a process for shrinking logs, but this one is too big to do it on since it requires doing a Transaction log backup and there isn't enough space to do one. What can I do to kill this file. I really don't care about any of the data in it, just need it off the drive as it is rapidly running out of space. I'm a developer with just enough knowledge of SQL Server to get myself in trouble so I don't want to break anything.

    Sean

  • Since you've posted this in the SQL 7/2000 forums, I'll assume you are using one of those versions, so you should be able to truncate the log with this statement and then shrink it...

    BACKUP LOG <db name> WITH TRUNCATE_ONLY

    To stop it growing again, have a read of Gail's article on managing the transaction log...

    http://www.sqlservercentral.com/articles/64582/

  • Or switch the database to simple recovery

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ian Scarlett (9/30/2011)


    Since you've posted this in the SQL 7/2000 forums, I'll assume you are using one of those versions, so you should be able to truncate the log with this statement and then shrink it...

    BACKUP LOG <db name> WITH TRUNCATE_ONLY

    To stop it growing again, have a read of Gail's article on managing the transaction log...

    http://www.sqlservercentral.com/articles/64582/

    What does that do exactly? That drive is so low on space at the moment, I'm afraid of filling it up and breaking the systems that work off of it.

    Thanks.

    Sean

  • Sean Grebey-262535 (9/30/2011)


    What does that do exactly? That drive is so low on space at the moment, I'm afraid of filling it up and breaking the systems that work off of it.

    There's a bit more to it, but essentially when you backup the transaction log, all the transactions are written to the backup file, then SQL Server throws away the transactions in the log, and starts again. If you backup using TRUNCATE_ONLY, it just throws the transactions away without actually backing them up, so you will be using no extra disk space.

    As Gail mentioned, you could switch to Simple recovery mode. That's a bit like doing an automatic backup with TRUNCATE_ONLY every time a transaction has been committed.

    Bear in mind, doing either of these things means you will no longer be able to recover the database to a point in time. All you will be able to do is restore the database to your last full or differential backup. Only you know if that is acceptable to your business.

  • Pretty much the same as switching to simple recovery. Discards all log records that are no longer needed, breaks the log chain. Lets you shrink the log back to a reasonable size (NOT 0).

    Afterwards you need to switch back to full recovery (if you switched to simple) and take a full or differential backup in order to restart the log chain and allow log backups and point-in-time recovery again.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply