locked
index rebuild (free space needed) RRS feed

  • Question

  • hi all,

    I'm upgrading to SQL 2012 from 2008R2, while doing so i will be rebuilding all the indexes on all the database. In my previous environment while doing so, i got space related error in primary filegroup for insufficient space in the primary filegroup. Is there any rule of thumb about how much space is required by index rebuild command for each database, or is there a safe threshold for free space in the database? Thank you
    Thursday, September 24, 2015 12:22 AM

Answers

  • When rebuilding a clustered index, there are many different factors that can change the amount of space needed. One thing to think about is if the operation is Online vs Offline, changes in index padding or fillfactor, if sorting is done in tempdb or the userdb, the maxdop settings (which can affect the end size), changes in compression,  concurrent user activity (if online), etc.

    There are a number of factors, and changes in any one of these can affect the size and intermediate space needed.


    Please click "Mark As Answer" if my post helped.

    • Proposed as answer by Tharindu Dhaneenja Thursday, September 24, 2015 9:38 AM
    • Marked as answer by pituachMVP Wednesday, October 7, 2015 5:01 PM
    Thursday, September 24, 2015 2:43 AM
  • There is no straight forward way to calculate it. Index rebuild requires roughly 1.5 times space as size of index. Of course factors like sort_in_tempdb would force sorting in tempdb thus reducing space requirements
    

    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 Article

    MVP


    • Edited by Shanky_621MVP Thursday, September 24, 2015 10:24 AM
    • Marked as answer by pituachMVP Wednesday, October 7, 2015 5:01 PM
    Thursday, September 24, 2015 10:24 AM
  • Note that if the rebuild is serial (which all scripts known to me are doing), then the free space should be based on the single largest index (including clustered) - not the sum size of all indexes. Just as a clarification. :-)

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by pituachMVP Wednesday, October 7, 2015 5:01 PM
    Thursday, September 24, 2015 11:14 AM

All replies

  • When rebuilding a clustered index, there are many different factors that can change the amount of space needed. One thing to think about is if the operation is Online vs Offline, changes in index padding or fillfactor, if sorting is done in tempdb or the userdb, the maxdop settings (which can affect the end size), changes in compression,  concurrent user activity (if online), etc.

    There are a number of factors, and changes in any one of these can affect the size and intermediate space needed.


    Please click "Mark As Answer" if my post helped.

    • Proposed as answer by Tharindu Dhaneenja Thursday, September 24, 2015 9:38 AM
    • Marked as answer by pituachMVP Wednesday, October 7, 2015 5:01 PM
    Thursday, September 24, 2015 2:43 AM
  • There is no straight forward way to calculate it. Index rebuild requires roughly 1.5 times space as size of index. Of course factors like sort_in_tempdb would force sorting in tempdb thus reducing space requirements
    

    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 Article

    MVP


    • Edited by Shanky_621MVP Thursday, September 24, 2015 10:24 AM
    • Marked as answer by pituachMVP Wednesday, October 7, 2015 5:01 PM
    Thursday, September 24, 2015 10:24 AM
  • Note that if the rebuild is serial (which all scripts known to me are doing), then the free space should be based on the single largest index (including clustered) - not the sum size of all indexes. Just as a clarification. :-)

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by pituachMVP Wednesday, October 7, 2015 5:01 PM
    Thursday, September 24, 2015 11:14 AM
  • Thank you all, that is what i was thinking.
    Thursday, September 24, 2015 12:53 PM
  • @Man-Of-Steel

    Can you please mark "Propose as answer" if your question is answered?

    Thursday, September 24, 2015 2:22 PM
  • @Man-Of-Steel

    Can you please mark "Propose as answer" if your question is answered?


    He would actually mark 'Mark as answer' OP cannot propose any answer.

    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 Article

    MVP

    Thursday, September 24, 2015 2:29 PM
  • 1). Sort in temp DB

    2). Check the Index Fill Factor Settings

    Regards,

    -Kranp.

    Thursday, September 24, 2015 9:41 PM