patternsqlMinor
Space Consumption for SQL Index Rebuild
Viewed 0 times
spacesqlrebuildforindexconsumption
Problem
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...
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)?
ALTER INDEX ALL ON Table_A REBUILDBefore 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)?
Solution
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_EXISTING will help here, somehow I don't think so.Context
StackExchange Database Administrators Q#13270, answer score: 4
Revisions (0)
No revisions yet.