Managing Transaction Logs

  • Excellent article! And thanks for clearing up, once and for all, the issue over full backups/log chain. It was always my understanding that full backups break the the log chain, and now I know this is not the case.

  • Thank you for the article.

    I found the section titled "How does SQL use the log?" particularly well written.

    I hope I am not the only one that feels this way, but one cannot go over these concepts enough times, no matter how much DBA experience one has.... 🙂

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Excellent

    Old article but still strikes the gold 🙂

  • Great post - very helpful!

    One question though - does backup order matter? For instance, if you have a FULL backup being done on Friday and differentials being done every other day, does the transaction log need to be backed up before the differential/fulls happens or after? Or does it not matter?

    Thanks,

  • It matters, but not for the reason you are asking.

    Although they are not required in your restore scenario, most back-up/recovery strategies would have T-log backups conducted on a more frequent basis than fulls and diffs. T-logs backups are typically much smaller and easier to maintain, requiring fewer resources. They are also an essential database maintenance activity.

    Bret

  • Tran log backups are unaffected by full/diff backups, so you can have your tran log backups running before, during or after the full/diff.

    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
  • Im assuming that there are a lot of considerations to account for when determining log backup frequency, but is a rule of thumb that says to perform them a few times a day or is it more of an hourly type of operation?

    Thanks,

  • Straight from the article

    Frequency of log backups

    The frequency that log backups should be done is dependent on two considerations:

    The maximum amount of data that can be lost in the case of a disaster

    The size to which the log can grow.

    The first consideration is by far the most important.

    When in full recovery model a disaster that destroys or damages the database can be recovered from without data loss, providing the transaction log file is available. If the log file is not available then the best that can be done, assuming that the log backups are stored separately from the database files and are available, is a restore to the last log backup taken, losing all data after that point. From this it should be clear that the interval between log backups should be carefully chosen based on the RPO (recovery point objective) requirements for that database. If the mandate is that no more than 20 minutes of data can be lost, then scheduling log backups hourly is a risk, as a disaster can result in losing up to 60 minutes of data if the transaction log file is lost or damaged.

    If (once the log backups have been setup and scheduled based on the database's RPO) the log is growing larger than is acceptable, then the log backup frequency can be increased in order to keep the size down. This is by no means guaranteed to have the desired effect as the log must be large enough to accommodate the largest single transaction run against the database (which is usually an index rebuild).

    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
  • Thank you so much for this article.

    I do have 1 question though in regards to sizing the log files. You mentioned a good starting point is to size each log file 150% of the size of the largest table. By this do you mean the data space or the index space? or the total of both?

Viewing 9 posts - 121 through 129 (of 129 total)

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