Post reply

ReIndexing without Log File Growth

  • October 15, 2008 at 11:46 am

    #194657

    Is there a way to rebuild indexes on a table without the log file growing too big. There is only one drive with 40GB space left, and the log file exists on a drive with 8 Gb free space and when I rebuild indexes with DBCC Rebuild on this table on the test server, the log file is getting full and the statement is being terminated.

    Do I need to create an additional log file on the 40GB drive, if so can I reclaim the space back??

    Thanks in Advance!!!

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • October 16, 2008 at 8:17 am

    #885863

    Change the recovery model to BULK_LOGGED just before reindexing.

    After reindexing, revert to FULL.

  • October 16, 2008 at 10:01 am

    #885948

    I hope this does not break the log chain.This needs to be done on Prod Db, that's the reason I am leery about this. In case I switch the recovery models do I need to take a full backup on I am back to FULL Recovery model from Bulk-Logged??

    Thanks Again!!!

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • October 16, 2008 at 10:12 am

    #885957

    Not exactly - It doesn't break the chain, but point-in-time restores are not available while in bullk-logged mode.

    This is a decent summary of the differences:

    http://technet.microsoft.com/en-us/library/ms189275.aspx

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 16, 2008 at 10:41 am

    #885972

    Well I wouldn't need to do point in time restore as I am reIndexing while in the maitenance window.Also it would be started after the full backup is taken.Does this Bulk Logged model require me to take T-Log backups ( my usual practice is hourly log backups) while reIndexing??

    Thanks

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • October 16, 2008 at 10:48 am

    #885976

    Require? No, but you can certainly stay on the same schedule.

    If you mean do I need to back up the log files to free up the space inside of the t-logs, then yes - during bulk-logged as during Full recovery, transactions are retained in the log until backed up (and replicated if that is set up).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 19, 2008 at 3:41 pm

    #886934

    Do take a database backup of after changing the recovery model

  • October 20, 2008 at 7:42 am

    #887111

    Yeah, for some bigger indexes I have got into the routine of setting the DB to simple recovery model, running the reindex, and then reverting to full recovery prior to the daily full backup.

    I would assume you have to do this out of normal hours or during scheduled downtime to prevent the tables locking up anyway, so just take a log backup before you change the recovery model and you'll be covered.

  • September 24, 2010 at 1:34 pm

    #1227204

    Matt Miller (#4) (10/16/2008)

    Not exactly - It doesn't break the chain, but point-in-time restores are not available while in bullk-logged mode.

    This is a decent summary of the differences:

    http://technet.microsoft.com/en-us/library/ms189275.aspx

    I was thinking about doing this but our production data is being replicated. Do you see any issues?

  • September 25, 2010 at 2:54 am

    #1227352

    No issues.

    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 25, 2010 at 8:01 am

    #1227383

    I've added this to our defrag process last night and the log file didn't grow much at all.

    Thank you.

Viewing 11 posts - 1 through 10 (of 10 total)

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