Tags Tags

Index Rebuild Maintenance Plan Grew MDF 5 Times Larger!

May 28th, 2012 - 04:03 pm ET by Abyss | Report spam
Help Create a new topic
I've been running some utilities from one of our vendors to purge older data. Their recommendation is to run these utilities and then to create/run the index rebuild maintenance plan, which I did. We're running SQL Server 2008 Enterprise and I set the maintenance plan to sort in tempdb an dto keep the index online. It took about 4 hours to run and when completed, my 30GB mdf file turned into about 80GB. This freaked me out and I shrank the database, knowing that this would basically undo the reindex process. The shrink didn't bring me back to the original 30GB. I ran the maintenance plan again and my mdf file jumped up to 170GB. How is it that this happens and that I can't reclaim the free space? Shouldn't the original indexes be removed when the new one comes online? One thing to note is that the original fill factor was 100%, which I changed to 90. I can't imagine this being the result of going from 30 to 170GB. I won't be running the maintenance plan again and have also disabled it. Disk space is a huge concern in this particular case, but rather the effects this might have on my system's performance. Any ideas and help would be greatly appreciated.
email Follow the discussionReplies 4 repliesReplies Make a reply

Replies

#1 Erland Sommarskog
May 28th, 2012 - 05:44 pm ET | Report spam
Abyss () writes:
I've been running some utilities from one of our vendors to purge older
data. Their recommendation is to run these utilities and then to
create/run the index rebuild maintenance plan, which I did. We're
running SQL Server 2008 Enterprise and I set the maintenance plan to
sort in tempdb an dto keep the index online. It took about 4 hours to
run and when completed, my 30GB mdf file turned into about 80GB. This
freaked me out and I shrank the database, knowing that this would
basically undo the reindex process. The shrink didn't bring me back to
the original 30GB. I ran the maintenance plan again and my mdf file
jumped up to 170GB. How is it that this happens and that I can't reclaim
the free space? Shouldn't the original indexes be removed when the new
one comes online? One thing to note is that the original fill factor was
100%, which I changed to 90. I can't imagine this being the result of
going from 30 to 170GB. I won't be running the maintenance plan again
and have also disabled it. Disk space is a huge concern in this
particular case, but rather the effects this might have on my system's
performance. Any ideas and help would be greatly appreciated.



When you rebuild an index, you need space for both instances of the
index. Sort in tempdb only means that the sorting takes place there.
So if the database fill is full, the file has to grow to accomodate
the reindex operation.

Be careful with maintenance plans; there is no reason to rebuild an
index unless there actually is fragmentation. Of course, if old rows have
been purged, that may reason for reindexing, since this may have
left you with lots of half-full pages, in case purge was not along the
clustered index.

I cannot comment on the specific case, since I have no data to work from.
But if you have a backup, you can use sys.db_dm_index_physical_stats
to compare the situation before and after.



Erland Sommarskog, SQL Server MVP,

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sql...14207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sql...95970.aspx

Similar topics

Make your own search :