Post reply

Understanding the logs

  • July 20, 2005 at 5:01 pm

    #88210

    Hi,

    I'm still a bit raw when it comes to SQL transaction logs and want to understand what I've already set up

    Overnight a DB does a full back-up.

    Throughout the day the log (via a maintenance plan) is copied/backed up to a separate server.

    When the log is backed up, is all data committed to the DB at the same time? I notice that the log size (trn file) can be anywhere between 100mb and 1.5gb.

    My main question is.... were the database to go down and I needed to restore would I need to restore the DB from the bak file and then apply ALL the log files backed up during the day?

    I want to learn more about when and how data is written to the DB. Does the dba have any influence over this? Would they want to have any?

  • July 20, 2005 at 5:09 pm

    #575939

    I guess for clarity, Are you doing differential backups in your maintenance plan?, or Log Backups?

    If it is log backups then you do have to apply all the logs in order.

    If it is differential backups, then you just need to apply the Last backup.

     

  • July 20, 2005 at 7:41 pm

    #575966

    Thanks, yes full backups.

    I've read that I have apply the logs sequentially using the norecovery switch for all but the most recent log backup?

    Still interested from hearing from anyone who can point me to a good article that explains what happens when the log is backed up... e.g. are non-written transactions written and then removed from the log to preserve space etc.

  • July 21, 2005 at 2:45 am

    #575995

    When a tlog backup is done, all the committed transactions are written to the TRN file. Depending on your backup statement, these committed transactions are truncated from the log. However the log size isn't reduced automatically as one would expect the log usage to be constant over a day.

    Uncommitted transactions are left in the log, which mark the latest part of the log which can be backed up.

    Consider a log representation:

    t1c:t2c:t3c:t4u:t5c:t6c:t7u...

    where t1c is a committed tran and t4u is an uncomitted tran.

    A tlog backup would only write t1c:t2c:t3c to the TRN file, leaving the first tran in the servers log as t4u.

    Yes, you must restore your tlog's in sequential order with the nonrecovery switch.

    HTH

    Dan

  • July 21, 2005 at 3:03 pm

    #576244

    Excellent - thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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