Home Forums SQL Server 7,2000 Backups Nightly Job Failing to Truncate Transaction Log RE: Nightly Job Failing to Truncate Transaction Log

  • As a general note, I suggest looking through this free book (free when downloaded as PDF): SQL Server Transaction Log Management by Tony Davis and Gail Shaw. It's the best source of info on this topic and I wish I had a copy back when I was a DBA.

    Moving on...

    What is the purpose of having a FULL recovery model if you are only aren't doing transaction log backups throughout the day? A FULL recovery model allows you to do restore your DB back to to the last transaction log backup. You are doing the DB and log backup at the same time, the backed up log file is essentially worthless. If you need point-in-time restores then you should stop truncating the log manually and increase the frequency of your log backups. If you don't need the ability to do point-in-time restores then change the recovery model to SIMPLE. a SIMPLE recovery model will automatically (well, usually :rolleyes:) manage your transaction log for you.

    Edit: First, did not see Louis' reply when posting this... Also fixed some bad grammar.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001