Index Rebuild Maintenance Plan Grew MDF 5 Times Larger!

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.

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

Thanks Erland. I'm pretty sure that this is why they wanted a rebuild
vs. a reorg on those indexes. Prior to the first purge, the database was
close to 80GB and after the final purge, we were just under 30GB. I
wanted to get through all of the purges (we were running multiple
processes per night) prior to running an index rebuild. Any thoughts on
how I can reclaim that 140GB?

Abyss writes:
> Thanks Erland. I'm pretty sure that this is why they wanted a rebuild
> vs. a reorg on those indexes. Prior to the first purge, the database was
> close to 80GB and after the final purge, we were just under 30GB. I
> wanted to get through all of the purges (we were running multiple
> processes per night) prior to running an index rebuild. Any thoughts on
> how I can reclaim that 140GB?

So did you use sys.db_dm_index_physical_stats? You can't do anything
until you have gathered information.

Here is a query against the old sysindexes table that I often use, which
shows which indexes that takes up place.

SELECT dbname = db_name(), [Table] = o.name, i.indid, [index] = i.name,
i.rows, i.reserved, i.dpages, i.used
FROM sysindexes i
JOIN sys.objects o ON i.id = o.object_id
WHERE indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND i.indid IN (0, 1)
ORDER BY i.reserved DESC

140 GB may be excessive, but 30 GB is probably way too low for this
database. Since you purge data, I guess you also add new data, and
if you don't preallocate space, the database will autogrow which
can cause outages and fragmentation if it happens unplanned. You
will also need space for future index rebuilds.

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