SQL Server Performance

Question regarding transaction log backups

Discussion in 'SQL Server 2008 General DBA Questions' started by reighnman, Apr 29, 2011.

  1. reighnman New Member

    We've recently switched the recovery model our production database from bulk-logged to full and I was curious on the recommended intervals for transaction log backups.
    Under the bulk model we we're running backups every hour, in order to give us hourly restore intervals. Now that we're doing backups of full transaction logs that can do point-in-time restores, is it still necessary to backup every hour or can it be reduced to say every 6 hours or once a day?
    I guess I'm looking for the best practice when it comes to transaction log backups.
    Database is 20GB
    TLog is 8GB (initial size)
    Any input would be great, thanks!
    reighnman, Apr 29, 2011
    #2

  2. Luis Martin Moderator

    Luis Martin, Apr 29, 2011
    #3

  3. reighnman New Member

    Thanks for the response, unfortunately that particular post is unrelated as to the intervals in which the trans logs should run under a full recovery model. If I didn't mention it before, we run Full backups nightly. This is more about whether or not I should continue running translog backups in short intervals through the day, etc.

    reighnman, Apr 29, 2011
    #4

  4. amu_27 New Member

    It should be based on your SLA with business on worst case secnario what is the minimum data loss they like to keep it. If your business users says that they can take half an hour worth of data loss than you should have log backup running every half an hour.
    amu_27, Apr 29, 2011
    #5

  5. reighnman New Member

    These are transaction log backups of a full recovery model, I can restore to any point-in-time within the log period since the last full backup.
    I could run 1 giant trans log at the end of the day and be able to restore to any second..
    That is why I'm wondering if it's a performance loss to run backups in short intervals since it's not really necessary aside from log space limitations.
    reighnman, Apr 29, 2011
    #6

  6. Jahanzaib Member

    Take first full backup at night and take transaction backup and then truncate log file,you can do this in a day and do transaction log backup routinely
    After truncation your log size reduced and then backup time will also reduced
    Jahanzaib, May 9, 2011
    #7

  7. satya Moderator

    Reighnman
    I beleive that the backups should never be a performance problem unless the underlying disks are in bad shape to write the backup contents.
    Just think about DR situation where you need to get the database online quickly, restoring a GB+ worth of Tlog file will take hours depending upon the server configuration. So it is ideal to have every hour transaction log backups + full or diff. backups to recover the data quickly.
    satya, May 10, 2011
    #8

  8. Jahanzaib Member

    Backups must be a performance hit because you are getting the resources and telling the processor to do this

    Take Full Backup weekly
    Take Differential Backup nightly
    Take log Backup hourly

    as AMU_27 mentioned it must be depend on the SLA or OLA we can say in your organization.you must plan about the space,data loss bearing policy etc
    Jahanzaib, May 27, 2011
    #8

Share This Page