3

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

  • I don't think there is a way to know exact size (1.5 is the rule of thumb as @Aaron Bertrand points out). You might try rebuilding one index at a time instead of all of them. – EBarr Feb 16 '12 at 19:54
4

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.

| improve this answer | |
  • Perfect, just looking for a rule of thumb. I am pushing to obtain some additional drive space and need to have some idea of what I'd need at a minimum. Thanks! – unforgiven1987 Feb 16 '12 at 21:23
0

You are using index all - have you tried rebuilding individual indexes one at a time?

| improve this answer | |
  • Its a good idea and I thought about it, but you can't regain the space consumed when rebuilding the indexes and still keep the indexes in a defragmented state. – unforgiven1987 Feb 16 '12 at 21:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.