April 20, 2009 at 11:45 am
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
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
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
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
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
The size calculation for all the indexes is the same. A little over 2x the index size since it is a transactional process.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply