Post reply

How big will the transaction logs get?

  • January 9, 2009 at 8:08 am

    #198199

    Hello Everyone,

    We have a production database running in simple mode. We want to put it in full recovery mode and start backing up the transaction log.

    Before we put it in full recovery mode I would like to get an idea of how much disk space will be required to store the transaction log backups.

    Is there a way to get an idea on this?

  • January 9, 2009 at 8:22 am

    #924226

    Mat Cottrell (1/9/2009)

    Is there a way to get an idea on this?

    How long is a piece of string?

    It depends on the activity in your database, the frequency of your log backups and how long you need to retain those log backups for.

    If you can say how often your full backups are, how often you're planning on backing up the transaction log, and aprox how much log activity occurs during the interval between log backups, then the total storage can be estimated.

    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

  • January 9, 2009 at 8:28 am

    #924235

    This big much as my son would say


    Shamless self promotion - read my blog http://sirsql.net

  • January 9, 2009 at 8:29 am

    #924239

    Thanks for the reply.

    So is there a way to measure the database activity?

  • January 9, 2009 at 9:01 am

    #924279

    Sure, enable full mode, run log backups, note size.

    There's no downside to doing this. Change it during the day, set up backups every 15 minutes and keep track of the sizes. Set an outlook reminder for every 15 minutes to bother you for a half day.

    If sizes are large, go back to simple, run a full, and then evaluate the disk space you need.

  • January 9, 2009 at 12:36 pm

    #924496

    Steve Jones - Editor (1/9/2009)

    There's no downside to doing this. Change it during the day, set up backups every 15 minutes and keep track of the sizes. Set an outlook reminder for every 15 minutes to bother you for a half day.

    Note that you need to take a full backup after switching to full recovery, before you can start taking log backups. The full backup starts the log chain.

    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

Viewing 6 posts - 1 through 6 (of 6 total)

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