Truncating transaction logs

  • In SQL 2005 we use this script to truncate Trans logs.

    dump tran dbname with truncate_only

    This is no longer a available in SQL 2008. What would be the equivalent command or how do I

    accomplish the same thing SQL 2008?

  • set recovery mode to simple

    issue a checkpoint

    set recovery mode to full

    take a full or differential backup

    The last step is vital because you have broken your log chain and will not be able to roll through log backups for recovery. Avoid truncating your log in production scenarios so I would question why you want to do this?

    dump is also deprecated so use backup, and use log rather than tran.

    ---------------------------------------------------------------------

  • Better still, since you can't use point in time recovery while truncating transaction logs, just set your database to simple recovery and leave it there. Better that than having to fight with incorrect backups.

    If you really do want to keep your database on full recovery, then you should just want to work with the transaction log and transaction log backups so that you can ensure that point in time recovery. That's the best approach.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Why are you truncating your transaction logs?

    Please read through this - Managing Transaction Logs[/url]

    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
  • My database is 9gig, my log file is 50gig and growing. I back up my database 3 times daily to tape and every 1/2 hour to disk on another server.

    The thinking is I don't need to save unneeded transactions.

    I'm new to this so thanks for anything you can offer.

  • If three times a day backup is adequate and there is no need for point in time recovery, set the database to simple recovery and leave the logs alone.

    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
  • ma29 (2/14/2011)


    My database is 9gig, my log file is 50gig and growing. I back up my database 3 times daily to tape and every 1/2 hour to disk on another server.

    What sort of backups are we talking here? If the database is in full recovery mode and you're not performing transaction log backups then it isn't surprising you've got a 50Gb log file!

  • I wrote this article on log backups just so I could use it in situations like this. Go here and read this[/url]. Hopefully it will help.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks to all for the information.

    Right now in a test environment we set up a maintenance plan that does a full backup on Sunday nights, Differentials every 2 hours and Tran logs every 20 min. We are in full recovery mode and all backups will eventuall go to directories on the recovery server. This way we can do point in time restores and add to that a tail log.

    Hopefully this solves the problem of the trans logs growing out of control. Once we are satisfied with the performance in the test env. we will put this into production.

    How does that sound, do you think the interval between Full, Diff and Log work?

    Lastly is there anything that can be done to clear out destination directories of backup files that are not needed, besides manually deleting them or writing a script to clear them out?

  • Every 2 hours is a little often for diff backups. Done so that you can restore the database quickly to any point?

    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 10 posts - 1 through 9 (of 9 total)

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