Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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

share|improve this question
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

migrated from stackoverflow.com Feb 16 '12 at 23:24

2 Answers

up vote 3 down vote accepted

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.

share|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
up vote 0 down vote

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

share|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
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – Marian Sep 18 '12 at 19:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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