Post reply

Trans Log Maintenance Strategy

  • January 23, 2009 at 3:14 pm

    #130329

    Hi,

    I'm trying to implement an efficient way to manage trans log in a prod environment (recovery model full) with the following backup strategy:

    - hourly trans log backups

    - nightly full backups

    Any help is greatly appreciated.

    Thanks,

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • January 23, 2009 at 3:28 pm

    #932087

    I found this by Paul Randal, I believe in yesterday's daily newsletter.

    http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

    This is on Gail's website:

    http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/

    A lot of responses to a question like this is the infamous "it depends". You don't necessarily do log backups to manage the size. You do it for recovery purposes. If you don't need a point in time recovery and can live with last night's full backup, put the database in simple recovery mode. If it's in full recovery mode for business reasons, how much data loss can your users tolerate? With your setup they can potentially lose one hour of data. If they can only tolerate 15 minutes, your strategy will not meet the expectations. You need to find out the business needs and go from there. The good news is, you're doing full backups and periodic log backups - definitely a good thing! You're on the right path.

    By the way, be sure to restore them to a test environment from time to time to verify the backups are good.

    -- You can't be late until you show up.

  • February 2, 2009 at 12:12 pm

    #936640

    Terry,

    Thanks for both references they were really helpful and my apologies for the delay in my reply.

    - Francis

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • February 2, 2009 at 12:22 pm

    #936646

    You're welcome, glad I could help!

    -- You can't be late until you show up.

  • February 2, 2009 at 12:40 pm

    #936668

    To add to what Terry posted try this article here (blatant self-promotion)

    http://www.sqlservercentral.com/articles/64582/

    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 5 posts - 1 through 4 (of 4 total)

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