Post reply

Best Practice for Managing Transaction Log File

  • September 30, 2010 at 9:18 am

    #239076

    Hi,

    What is the best way managing tlog from growing so large during a weekly maintenance?

    1.- Is it to set the recovery mode to simple

    - do reorganize index

    - rebuild index

    - update stat

    - set recovery mode to full

    - shrink tlog file

    - do a full backup

    2.Do a more frequent backup on the tlog file (maybe every 15mins)

    - do reorganize index

    - rebuild index

    - update stat

    If you have a better way of doing managing, please share.

    Thanks,

  • September 30, 2010 at 11:01 am

    #1229741

    Definitely, absolutely not number 1.

    Set recovery to bulk logged if that's an option. Note the recovery limitations.

    Rebuild only indexes that need rebuilding

    Make sure that the tran log is large enough to accommodate that.

    Set recovery back to full

    Take log backup

    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

  • October 1, 2010 at 8:47 am

    #1230267

    I got you... Thank you!

    When do you do reorganize index and stat update?

Viewing 3 posts - 1 through 2 (of 2 total)

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