Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Transaction Log Backup

    How often do you guys back up your trans logs, every 10mins, 30mins or hourly???

    Just curious

  2. #2
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Depends on the requirements. Some we do every 10 minutes, some every hour, some every couple of hours.

    What's your acceptable data loss window?
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Des ... it really depends on the business. If the business model is highly transactional, you would want more frequent tran log backups.

    Currently we take hourly tran log backups on some databases, and only daily backups on others ... but this is not super high volume. Another place I worked, we took 5 minute tran backups, simply because of the volume of customer data coming in, and a business decision to only lose no more than 10 minutes worth of transactional data.

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Transaction log device should stay the same size, so the real determinant is the amount of data modifications that occur on a periodic basis. Based on that amount, you adjust the frequency on log dumps to ensure that the log does not start growing before your next scheduled backup. The frequency on log dumps does not have anything to do with business requirements, because even if I am backing up the log once a day, - I can still recover to a point in time.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Quote Originally Posted by rdjabarov
    The frequency on log dumps does not have anything to do with business requirements...
    This has never, in my entire career, ever been true.

    Quote Originally Posted by rdjabarov
    ...even if I am backing up the log once a day, - I can still recover to a point in time.
    Lemme get this straight. You're backing up the transaction log once per day, and you can recover to any point in time?

    So... if you backup your tranlog at noon on Monday, and Tuesday at 11:00 AM, the server disk array completely dies, you're telling me that you can recovery to Tuesday at 10:59 AM on another server or disk? Please teach me how to do this.

    I really hope I'm misunderstanding you.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I hear challenge in the air...Which disk array? Be more specific.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Which disk array? The one with the database on it.

    For purposes of illustration, assume this scenario, with a transaction log backup once per day.

    Your server has a full backup on Sunday at noon. You take a transaction log backup on Monday at noon. On Tuesday at 11:59 AM, Mr. Newb Sysadmin takes the server offline and formats the hard drives. Are you telling me that you can recover the database to Tuesday at 11:59 AM? If so, then please tell me how this is accomplished. Otherwise, please tell me I misunderstood you.

    If your company is comfortable re-entering 24 hours worth of data in the event of a server failure, that's fine. Mine isn't. Not all databases require frequent transaction log backups, and most that I work with don't require any at all. But saying that business requirements don't have anything to do with log backup frequency is just dangerous and wrong. Business requirements have a great deal to do with your backup scheme.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by tomh53
    Des ... it really depends on the business. If the business model is highly transactional, you would want more frequent tran log backups.

    Currently we take hourly tran log backups on some databases, and only daily backups on others ... but this is not super high volume. Another place I worked, we took 5 minute tran backups, simply because of the volume of customer data coming in, and a business decision to only lose no more than 10 minutes worth of transactional data.

    Thank you Tom thats basically what I was thinking, but i wanted to get confirmation.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I don't like when I am given a list of possible answers that I can chose from, that's probably why I am not good at taking tests (including certification exams). If you lost every single drive, - then taking trx log dump once a day will force you to restore to the last full + last diff (if any) + all trx logs available since either last full or diff. If, however, you lost your data device drive(-s) while the disk that holds your trx log is still up, - yes, I can recover everything up to the time of failure, even if I take trx log dump once a day, and the last one was taken almost 24 hours ago. BTW, at a very minimum, you should have suggested that there is a diff backup taken on Monday night/Tuesday morning, otherwise your scenario is not very realistic.

    The point I was trying to make when presenting an example with one trx log dump a day was to stress that business requirements do not dictate the frequency of log dumps, and not to say that you should take only 1 log dump a day. In 99% of cases you would be better off just doing a full/diff backup instead. The frequency of log dump operations should be dictated by the amount of data modified, which in itself dictates how much of usefull transactions the business will have to wait for to be restored from each backup. Frequent trx log dumps tie up IO threads and memory. This is not needed in environments where it is determined that the amount of data being modified does not exceed around 10MB. Infrequent backups may lead to long-lasting backup process with hundreds of megabytes (and I've seen gigabytes) of data being backed up. During those times transaction processing is slowing down considerably, because while users are trying to write to the log (see basic concepts of storage engine for further details), system spid is waiting on the kernel-mode thread(-s) to read from the same log device, which obviously leads to kernel level thread collision over the read/write disk heads, and may lead to disk queue length to go through the roof on that disk. So, the first thing that needs to be determined is how much data gets modified on a periodic basis. Only this will tell you how frequently you need to run trx lof dumps...Whew, that was long, but useful I hope
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So long and short of it, you prefer doing more frequent differential backups to ensure recovery, rather than log dumps?
    By the way, I would disagree with one point, based upon definition. I would say that "Business Requirements" do impact disaster recovery and other routine administrative tasks, but "Application Requirements" would not.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Nah, I mention them because they do exist and are (or should be) used more often than they are now, but not because I prefer them over trx dumps. Each type of backup does exist for a specific purpose, and should be used for it.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Sep 2005
    Posts
    161
    I agree with rdjabarov with one clarification. We must be talking about a typical setup where the data is stored on a raid 5 (or raid 10), and the transaction log is stored on a separate raid 1. If the transaction log is stored on the same array as the data, all bets are off. I have recently helped a client where the data file had become corrupt. With last night's backup and the transaction log in tact, there was almost no loss of data. We recovered everything up to the last transaction log checkpoint.

    If you have a "busy" database, transaction log dumps do consume resources. I've had users in one scenario who learned that on the hour, every hour, the application would slow down for a few minutes. They didn't know why, but they did notice...

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Business requirements tell us how much time can a business aford to declare as unrecoverable in terms of personnel time spent working on the system. Those requirements have to be adjusted by the information presented by a DBA about volume of data being modified during the specified period of time. In 9 out of 10 the requirements were altered based on this information to favor system availability and performance.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •