Post reply

Running Maintenance Plan to INDEX REBUILD, impact on transaction logs

  • September 9, 2010 at 11:02 am

    #229206

    Hi,

    Wanting to schedule an INDEX REBUILD on all tables on SQL 2005 SE during downtime window, but conscious that INDEX REBUILD will impact the transaction log. We can monitor the transaction log for free space with "DBCC SQLPERF(logspace)", increase transaction log backup frequency during this operation, and allow the transaction log to grow if needed, but what if it still approaches the capacity of the drive?

    Can the maintenance plan be safely killed during execution with no damage to work in progress, and if so, would this be done by killing the process on activity monitor?

    Probably being paranoid here, but I don't want to get a great view of a train crash approaching which I just have to sit and watch.

    (Option to alter recovery mode to SIMPLE or BULK LOGGED is not preferable).

    Thanks

    Andrew

  • September 9, 2010 at 11:19 am

    #1218813

    Check this thread, you may get some ideas.

    http://www.sqlservercentral.com/Forums/Topic624723-5-1.aspx#bm624760

  • September 9, 2010 at 12:10 pm

    #1218835

    Andrew-443839 (9/9/2010)

    (Option to alter recovery mode to SIMPLE or BULK LOGGED is not preferable).

    Why don't you want to switch to bulk logged?

    Not wanting to switch to simple I can understand (broken log chain), but I don't know why you don't want to switch to bulk logged. Since rebuilds are minimally logged in recovery models other than full, that would probably be the best bet for keeping the log under control.

    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

  • September 10, 2010 at 2:29 am

    #1219027

    GilaMonster (9/9/2010)

    Why don't you want to switch to bulk logged?

    I just have concerns about regular maintenance jobs changing the recovery mode - when the mode is changing routinely, it carries risk of it being left in the wrong state.

    But from going through the other linked post, I think we may need to try out a Bulk Logged approach on our Test environment.

    Thanks again for all replies,

    Andrew

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

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