Truncation of database logs

  • Hi All

    I am having a database, where we every night takes a full backup. The database has run for some time now and we have discovered that the log file is not truncated.

    I tried to make another backup and then shrink the database and it did not help. Then I looked through my SQL book and discovered that the database is only truncated when a logfile backup is made.

    My question is now, how do I design a backup scheme? We do not need a hourly backup of the log files and actually we only need the full backup. So should I after I made my fullbackup also make a log file backup, so the log files is truncated?

    Hope you have some ideas

    regards

    Bo

  • bdh (8/11/2008)


    Hi All

    I am having a database, where we every night takes a full backup. The database has run for some time now and we have discovered that the log file is not truncated.

    I tried to make another backup and then shrink the database and it did not help. Then I looked through my SQL book and discovered that the database is only truncated when a logfile backup is made.

    My question is now, how do I design a backup scheme? We do not need a hourly backup of the log files and actually we only need the full backup. So should I after I made my fullbackup also make a log file backup, so the log files is truncated?

    Hope you have some ideas

    regards

    Bo

    Hi Bo,

    You say that "actually we only need the full backup". If it is enough for you to recover to the last nights full backup, and you do not need any further recovery granularity, you may want to switch to simple recovery mode. (although I'd just set up the log backups and stay in full recovery mode)

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Hi - are you *sure* you don't need transaction log backups? If this is an OLTP database, this would mean if you have a problem during the day, you have no way of restoring to a point in time just before whatever caused the failure - meaning you have lost all data from the point of your last full backup. Even if your transaction load is low, a point in time restore from the backup and logs is going to be faster and a lot less hassle then re-keying. If - however, this is for example a reporting database updated once a day - then you can set up the database in Simple Recovery mode and configure the backup to run once your dataload has completed see http://msdn.microsoft.com/en-us/library/aa173563.aspx

    hth

    Andrew

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • How much data loss is acceptable if a drive fails or the database becomes corrupt?

    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
  • Very business oriented Gail? Aggree with all though have found (rightly or wrongly that there is more then one way to skin a 't-log').

    If you set yourself a task, like once a week, either ooh or at low peak (if you have either), take a full backup of the database, then set recovery to simple and then manually shrink the log.

    Then just switch back to full recovery and dont loose the backup for a bit.

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • Adam Zacks (8/11/2008)


    Very business oriented Gail? Aggree with all though have found (rightly or wrongly that there is more then one way to skin a 't-log').

    If you set yourself a task, like once a week, either ooh or at low peak (if you have either), take a full backup of the database, then set recovery to simple and then manually shrink the log.

    Then just switch back to full recovery and dont loose the backup for a bit.

    And take a full backup after you have truncated the log and switched back to full recovery mode 🙂 Full recovery mode is not activated until a full backup has been taken!

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Andras Belokosztolszki (8/11/2008)

    And take a full backup after you have truncated the log and switched back to full recovery mode 🙂 Full recovery mode is not activated until a full backup has been taken!

    Regards,

    Andras

    And a link for further reading: http://msdn.microsoft.com/en-us/library/ms178052.aspx

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Ooops, yeah that would have been a problem. Soz

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • Adam Zacks (8/11/2008)


    Very business oriented Gail?

    Acceptable data loss is one of the main things that determines whether or not log backups are necessary and how frequent they should be. If 'acceptable' data loss is 15 minutes, then daily full backups and simple recovery mode isn't going to cut it.

    It's one of the things that doesn't get mentioned often. I'm sure it's no fun telling the big boss that 2 days of data has been lost because the log backups weren't set up properly. I certainly wouldn't want to be in that position.

    If you set yourself a task, like once a week, either ooh or at low peak (if you have either), take a full backup of the database, then set recovery to simple and then manually shrink the log.

    Then just switch back to full recovery and dont loose the backup for a bit.

    Why do you want to shrink the transaction log file weekly? All you're doing is forcing it to grow again, increasing the number of virtual log files (results on slower backups), causing file level fragmentation and bringing activity in the DB to a halt as the log file grows.

    Oh, and you need a full backup after changing back to full recovery, otherwise your log chain is broken. You will not be able to do point-in-time recovery after switching to simple until you take another full/diff backup.

    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
  • Andras Belokosztolszki (8/11/2008)


    And a link for further reading: http://msdn.microsoft.com/en-us/library/ms178052.aspx

    Regards,

    Andras

    Or my semi-rant on the matter - http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/

    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
  • I have worked with some systems before where there is barely enough disk to run the db's and logs and backup the db (locally) before deleting the previous nights backup. Obviously the backups then go to tape.

    I was talking about a work-around that i have used in the past to keep low tansactional, low resource systems running. I would say from details in this topic it sounds like this system may fit that 'box'.

    Havent ever had a bad crash or corruption on an OLTP database yet, so probably cant appreceiate the pain.

    But i bow my head to experience and will be quiet. Oh and Gail, I was playing... I love reading your responses with people and was just poking fun, thats all. Wasnt being nasty :'(

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • GilaMonster (8/11/2008)


    Adam Zacks (8/11/2008)


    Very business oriented Gail?

    You will not be able to do point-in-time recovery after switching to simple until you take another full/diff backup.

    Is that for the db, log or both? Thought it was the db but now doubting myself :S

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • Adam Zacks (8/11/2008)


    I have worked with some systems before where there is barely enough disk to run the db's and logs and backup the db (locally) before deleting the previous nights backup. Obviously the backups then go to tape.

    I helped out on one of those once, except the tape backup wasn't working.

    http://sqlinthewild.co.za/index.php/2008/03/06/on-recoverability/

    They're still picking up the pieces of that one.

    But i bow my head to experience and will be quiet. Oh and Gail, I was playing... I love reading your responses with people and was just poking fun, thats all. Wasnt being nasty :'(

    May I suggest more smilies? Tone is very hard to read in a post. 😉

    And please don't be quiet. That defeats the purpose of a forum.

    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
  • Adam Zacks (8/11/2008)


    Is that for the db, log or both? Thought it was the db but now doubting myself :S

    I don't quite understand the question. Elaborate more please?

    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
  • when you change back to 'full' rec-mode from 'simple', should you then bakup the db and log (seperately) or just the db? I have only backed up the db when i have done my 'shrinkage trick' 😉

    And on the other line, if you want more smilies, then you shall have them :hehe:

    Hows it feel to have a cyber-geek-fan? 🙂

    Adam Zacks-------------------------------------------Be Nice, Or Leave

Viewing 15 posts - 1 through 15 (of 39 total)

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