HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Space Consumption for SQL Index Rebuild

Submitted by: @import:stackexchange-dba··
0
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...

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)?

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.