I am attempting to rebuild the indexes for a single table on a SQL Server 2005 reporting database. The indexes are heavily fragmented as this has not been done for a long time. I am running the following query…
ALTER INDEX ALL ON Table_A REBUILD
Before building the index, the data is consuming 32 GB and the indexes 98 GB. I am unable to finish the rebuild as I am running out of space once the rebuild consumes an additional 80 GB.
Is there any way to determine how much space I will need to rebuild the indexes on this table (and eventually all tables within my db)?
Best Answer
Typical wisdom is 1.5x index size, plus a little fudge depending on fill factor and how full your current index pages are (in most cases I would expect it to be slightly less than 1.5x). But I would definitely not cut it close. If you don't have 150GB free on your drive, I would find a way to do it elsewhere. I don't know if
WITH DROP_EXISTINGwill help here, somehow I don't think so.