Optimization job causes database to grow

I have a scheduled optimization that interacts with a database (approximately 50GB) that when it runs, it causes it to grow significantly. I would think that the rebuilding of the indexes should compact the space, not expand it
The settings I have are:
index fill - 50
Remove unused space checke
> Shrink db when it grows beyong 50MB
> Leave 10% fre
The disk has 101 GB free, and the db should sit at about 40GB. Think the 50MB limit could be causing a problem
TIA
MikeMike,
First off a fill factor of 50% is usually too low for the average table.
That means each page will only be half full after the reorg and that is a
lot of wasted space. But to answer your question it's actually the
opposite of what you think. SQL Server needs lots of free space to rebuild
an index especially the clustered index. It essentially rebuilds the table
elsewhere in the database and then drops the original. So you want to
always have plenty of free space in the db at all times. Your actually
defeating the purpose of the reorg to some degree by telling the MP to
shrink the file when done. Shrinking the file causes data that is at the
end of the file to be moved to the beginning and will almost always
refragment your tables when doing so. There is no penalty for having too
much free space in the db but a big one for not enough. Turn off the
autoshrink and leave the db at a size that it finds comfortable and make
sure you always have plenty of free space.
--
Andrew J. Kelly
SQL Server MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:EB161D21-2302-4D30-A05A-C19066AF2853@.microsoft.com...
> I have a scheduled optimization that interacts with a database
(approximately 50GB) that when it runs, it causes it to grow significantly.
I would think that the rebuilding of the indexes should compact the space,
not expand it.
> The settings I have are:
> index fill - 50%
> Remove unused space checked
> > Shrink db when it grows beyong 50MB
> > Leave 10% free
> The disk has 101 GB free, and the db should sit at about 40GB. Think the
50MB limit could be causing a problem?
> TIA,
> Mike