locked
Index Rebuild RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    While rebuilding indexes log file is growing vary rapidly and we can't put DB in simple recovery mode because we have configured DB mirroring. Is there any way to minimize log file growth while index rebuild?

    We have created index rebuild job with the help of SSIS package and we are executing index job once in a month during off business hours. The DB size is around 240 GB.

    Monday, June 20, 2016 2:28 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    No optimizations if you have to be in full recovery (which you have to because of mirroring). The best you can do is to not rebuild indexes when they don't have to be rebuilt. I suggest using Ola Hallengren's maintenance solution or only the indexoptimize proc for this (ola.hallengren.com).

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, June 20, 2016 2:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    While rebuilding indexes log file is growing vary rapidly and we can't put DB in simple recovery mode because we have configured DB mirroring. Is there any way to minimize log file growth while index rebuild?

    We have created index rebuild job with the help of SSIS package and we are executing index job once in a month during off business hours. The DB size is around 240 GB.

    Adding to what Tibor said, index rebuild is fully logged in full recovery model. So your best bet is to ONLY rebuild indexes which are fragmented  > 30 % and make sure you don't rebuild small indexes, i.e one having page count < 2000

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles
    MVP

    Tuesday, June 21, 2016 7:29 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    No optimizations if you have to be in full recovery (which you have to because of mirroring). The best you can do is to not rebuild indexes when they don't have to be rebuilt. I suggest using Ola Hallengren's maintenance solution or only the indexoptimize proc for this (ola.hallengren.com).

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, June 20, 2016 2:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Praveen,

    I'm not sure how many transactionlog back ups you taking..  every day.

    Please let us know your backup& transactionlog backup times in 24hrs?

    If you run DBCC Loginfo,  --Status should show 2 in your case,, if you take TL backup and  then it will become 0..then size will decrease automatically decrease.

    Thanks

    Monday, June 20, 2016 2:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    you can build index one by one and after each index can take log backup to truncate log.
    Monday, June 20, 2016 3:02 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    While rebuilding indexes log file is growing vary rapidly and we can't put DB in simple recovery mode because we have configured DB mirroring. Is there any way to minimize log file growth while index rebuild?

    We have created index rebuild job with the help of SSIS package and we are executing index job once in a month during off business hours. The DB size is around 240 GB.

    Adding to what Tibor said, index rebuild is fully logged in full recovery model. So your best bet is to ONLY rebuild indexes which are fragmented  > 30 % and make sure you don't rebuild small indexes, i.e one having page count < 2000

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles
    MVP

    Tuesday, June 21, 2016 7:29 AM