Post reply

Rebuild index online

  • April 20, 2009 at 11:45 am

    #220244

    Hi all, if i choose to rebuild clustered index (120 GB in size) online and i choose sort_in_tempdb option how much space do i need for tempdb to be available?

    Also, i understand that the available space in db has to be 2.5 times the size of the index in order to rebuild online.

    Is that correct?

    Thanks

  • April 20, 2009 at 6:21 pm

    #981136

    If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the collection of sort runs for the largest index, typically the clustered index, and sufficient free space in the destination filegroup to store the final structures of all the indexes. This includes the clustered index that contains the data rows of the table.

    Check-->http://msdn.microsoft.com/en-us/library/ms188281(SQL.90).aspx

    2.5 times requirement looks to be okie.

    MJ

  • April 21, 2009 at 2:58 pm

    #981822

    trans54 (4/20/2009)

    Hi all, if i choose to rebuild clustered index (120 GB in size) online and i choose sort_in_tempdb option how much space do i need for tempdb to be available?

    Also, i understand that the available space in db has to be 2.5 times the size of the index in order to rebuild online.

    Is that correct?

    Thanks

    Yes It needs over twice the size of an existing index because Rebuilding takes place with the existing index as its base while dropping it at the final phase of the rebuild.

  • April 21, 2009 at 9:32 pm

    #981929

    Index rebuilds usually require ~1.5x of spare disk space of the largest index. This doesn't include the space used by the original index. Of course this would be more if SQL Server rebuilds indexes in parallel (if this is even possible).

  • April 21, 2009 at 9:46 pm

    #981930

    Thank you for replies.

    Is there a formula to calculate mapping index size for non unique clustered index when it does rebuild?

  • April 21, 2009 at 10:10 pm

    #981931

    The size calculation for all the indexes is the same. A little over 2x the index size since it is a transactional process.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 6 posts - 1 through 5 (of 5 total)

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