March 22, 2010 at 2:04 pm
I have a large table (approximately 188gb) with about 66gb of indexes.
I'd like to do an online reindex of this table, using the 'sort_in_tempdb = ON' parameter to rebuild the clustered index and others.
Everything Ive found indicates that I should 'make sure tempdb has sufficient space'.
Ive not found any guidelines on what constitutes 'sufficient' space though.
I have about 177gb free on the drive that stores tempdb + the free space in the tempdb data files (roughly 9gb more). Is the tempdb space I need relative to the largest index, or the overall table size? or is it more related to the log space in the database, and tempdb? switching the db to simple recovery mode for the duration of this event is not a viable option.
thanks in advance for your insight.
March 22, 2010 at 2:18 pm
I remember seeing a similar post. May be you could check this
http://www.sqlservercentral.com/Forums/Topic851669-146-1.aspx
M&M
March 22, 2010 at 2:24 pm
From BOL:
When you create a clustered index on a table that has nonclustered indexes, you must have available as free space:
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.
If I am reading this correctly, I should be good...
March 22, 2010 at 2:53 pm
also, thanks for the link, but Im not sure that is really relatable to my scenario.
I have a large table with a highly fragmented index set. My dynamic reindexing routine has excluded it because the time required for an offline reindex is not acceptable given the usage requirements of the environment.
my options are a) schedule an extended application outage and reindex offline, or b) reindex online.
If I'm reindexing online, I'd prefer to use the sort_in_temp_db option, but Im unsure how to accurately determine if I have adequate space in tempdb for this operation.
March 23, 2010 at 6:03 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply