Optimizing and shrinking large highly-transactional database

Hi,
I have a 50GB database on SQL Server 2000 Enterprise that is highly
transactional in a 24x7 environment. There's approximately 8 GB of
free space, so I want to shrink it. It has been online for over five
years and has never been defraged or reorganized. I've been given a
maximum downtime window of eight hours. A test defrag on a copy of the
database ran for almost two days (single user mode, quad-cpu server).
I can't risk deadlocks with this system (lots of foreign key
contraints, etc.), so I am unable to run the defrag while it is
online.
I'm wondering if moving all of the data to a new file in the same
filegroup would be faster and also eliminate the existing
fragmentation and possibly truncate the free space. Does anyone have
any suggestions?
Thanks!
First off I would highly recommend you DONT shrink the files. 8GB of free
space in a 50GB db is the minimum free space I would like to see. You need
free space to minimize fragmentation and for operations such as reindexing
to work properly. See here for more details:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
But two days to rebuild less than 50GB of indexes is pretty pathetic and I
suspect something else is wrong. Make sure the log files for the user and
tempdb databases are on a separate raid array than the data and preferably
on a Raid 1 or Raid 10. What is your current disk setup like? But if you
want to defrag everything you may get faster results by BCP'ing out all the
data in each table, truncating the tables and BCP or Bulk Inserting back in
again. I would drop all the non-clustered indexes before importing back in
and re add them afterwards. I would also use an Order by clause when
exporting to export them in the order of the clustered index to give the
best chance of a clean and fast import for the clustered index. If you
export the data to a disk other than the one the data or log files are on
this should be a relatively quick process. But again even if you simply
reindexed each table one at a time with 4 processors it should only take a
short while.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Curtis" <curtmorrison@.yahoo.com> wrote in message
news:1d585c72-be6c-4913-a18d-5e38c6aa1bcb@.w34g2000hsg.googlegroups.com...
> Hi,
> I have a 50GB database on SQL Server 2000 Enterprise that is highly
> transactional in a 24x7 environment. There's approximately 8 GB of
> free space, so I want to shrink it. It has been online for over five
> years and has never been defraged or reorganized. I've been given a
> maximum downtime window of eight hours. A test defrag on a copy of the
> database ran for almost two days (single user mode, quad-cpu server).
> I can't risk deadlocks with this system (lots of foreign key
> contraints, etc.), so I am unable to run the defrag while it is
> online.
> I'm wondering if moving all of the data to a new file in the same
> filegroup would be faster and also eliminate the existing
> fragmentation and possibly truncate the free space. Does anyone have
> any suggestions?
> Thanks!
|||On Nov 21, 3:50 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> First off I would highly recommend you DONT shrink the files.
After giving this some more consideration, I agree...

> What is your current disk setup like?
It looks like the data and logs are on different partitions on a SAN.
I don't have any additional seperate physical partitions to play with.

> But if you want to defrag everything you may get faster results by BCP'ing out all the
> data in each table, truncating the tables and BCP or Bulk Inserting back in
> again.
Exactly my thoughts in the first place, but the system is so complex
and so un-documented... this is a last resort... I think this may
Inevitably be the route I need to take.
|||Different logical partitions on the same physical array do not give any
performance benefits. When you do a resource intensive operation such as a
reindex the data and log activities will contend with each other on the same
physical array. One thing you can do to minimize this effect and to speed up
the overall operation is to put the database in Simple recovery mode so the
index rebuilds will be done with a minimally logged operation. That will
minimize the data going to the transaction logs. Make sure to have a valid
full backup before you start, change the recovery mode to Simple and then
rebuild all the indexes one table at a time with DBCC
DBREINDEX('TableName'). A 50GB db with a 4 processor system should only
take a few hours even with poor disks. Make sure the MAXDOP of the server is
set to 0 or 4 so you get maximum parallelism when rebuilding. I would test
this first on a copy of the db just to ensure you have it all down right.
Then change the recovery mode back to Full (if that's what you had before)
and make to take another FULL backup when done.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Curtis" <curtmorrison@.yahoo.com> wrote in message
news:313d59e5-33d9-4161-8d4c-1f7fa2b91c27@.v4g2000hsf.googlegroups.com...
> On Nov 21, 3:50 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
> After giving this some more consideration, I agree...
>
> It looks like the data and logs are on different partitions on a SAN.
> I don't have any additional seperate physical partitions to play with.
>
> Exactly my thoughts in the first place, but the system is so complex
> and so un-documented... this is a last resort... I think this may
> Inevitably be the route I need to take.
>
|||On Nov 22, 6:26 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
One thing you can do to minimize this effect and to speed up
> the overall operation is to put the database in Simple recovery mode so the
> index rebuilds will be done with a minimally logged operation. That will
> minimize the data going to the transaction logs.
Thanks Andrew - Your thought are exactly on track with what I have
already tried.
|||This is one of my favorite scenarios. We have the same problem almost except
we carry tables with 400mill rows and our window is smaller than yours.
What we end up doing is almost the same thing. We do a select into another
table, and create all the indexes on that new table. Then we just rename the
original table to OLD and then the new table gets the original name... did I
say that right?
1. select * into customerNEW from customer.
2. Build indexes on customerNEW.
3. rename customer to customerOLD.
4. rename customerNEW to customer.
5. Drop table customerOLD.
and you're done...
Of course, if you're lucky enough to be on Yukon, then you can partition and
reindex a single partition at a time and get much better results. That is if
you don't need the parallel reads that weren't added. However, you can get
concurrency back if you put a partitioned view on top of them. I know that's
a workaround for it, but it's all we've got until katmai.
"Curtis" wrote:

> Hi,
> I have a 50GB database on SQL Server 2000 Enterprise that is highly
> transactional in a 24x7 environment. There's approximately 8 GB of
> free space, so I want to shrink it. It has been online for over five
> years and has never been defraged or reorganized. I've been given a
> maximum downtime window of eight hours. A test defrag on a copy of the
> database ran for almost two days (single user mode, quad-cpu server).
> I can't risk deadlocks with this system (lots of foreign key
> contraints, etc.), so I am unable to run the defrag while it is
> online.
> I'm wondering if moving all of the data to a new file in the same
> filegroup would be faster and also eliminate the existing
> fragmentation and possibly truncate the free space. Does anyone have
> any suggestions?
> Thanks!
>
sql

Optimizing and shrinking large highly-transactional database

Hi,
I have a 50GB database on SQL Server 2000 Enterprise that is highly
transactional in a 24x7 environment. There's approximately 8 GB of
free space, so I want to shrink it. It has been online for over five
years and has never been defraged or reorganized. I've been given a
maximum downtime window of eight hours. A test defrag on a copy of the
database ran for almost two days (single user mode, quad-cpu server).
I can't risk deadlocks with this system (lots of foreign key
contraints, etc.), so I am unable to run the defrag while it is
online.
I'm wondering if moving all of the data to a new file in the same
filegroup would be faster and also eliminate the existing
fragmentation and possibly truncate the free space. Does anyone have
any suggestions?
Thanks!First off I would highly recommend you DONT shrink the files. 8GB of free
space in a 50GB db is the minimum free space I would like to see. You need
free space to minimize fragmentation and for operations such as reindexing
to work properly. See here for more details:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
But two days to rebuild less than 50GB of indexes is pretty pathetic and I
suspect something else is wrong. Make sure the log files for the user and
tempdb databases are on a separate raid array than the data and preferably
on a Raid 1 or Raid 10. What is your current disk setup like? But if you
want to defrag everything you may get faster results by BCP'ing out all the
data in each table, truncating the tables and BCP or Bulk Inserting back in
again. I would drop all the non-clustered indexes before importing back in
and re add them afterwards. I would also use an Order by clause when
exporting to export them in the order of the clustered index to give the
best chance of a clean and fast import for the clustered index. If you
export the data to a disk other than the one the data or log files are on
this should be a relatively quick process. But again even if you simply
reindexed each table one at a time with 4 processors it should only take a
short while.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Curtis" <curtmorrison@.yahoo.com> wrote in message
news:1d585c72-be6c-4913-a18d-5e38c6aa1bcb@.w34g2000hsg.googlegroups.com...
> Hi,
> I have a 50GB database on SQL Server 2000 Enterprise that is highly
> transactional in a 24x7 environment. There's approximately 8 GB of
> free space, so I want to shrink it. It has been online for over five
> years and has never been defraged or reorganized. I've been given a
> maximum downtime window of eight hours. A test defrag on a copy of the
> database ran for almost two days (single user mode, quad-cpu server).
> I can't risk deadlocks with this system (lots of foreign key
> contraints, etc.), so I am unable to run the defrag while it is
> online.
> I'm wondering if moving all of the data to a new file in the same
> filegroup would be faster and also eliminate the existing
> fragmentation and possibly truncate the free space. Does anyone have
> any suggestions?
> Thanks!|||On Nov 21, 3:50 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> First off I would highly recommend you DONT shrink the files.
After giving this some more consideration, I agree...
> What is your current disk setup like?
It looks like the data and logs are on different partitions on a SAN.
I don't have any additional seperate physical partitions to play with.
> But if you want to defrag everything you may get faster results by BCP'ing out all the
> data in each table, truncating the tables and BCP or Bulk Inserting back in
> again.
Exactly my thoughts in the first place, but the system is so complex
and so un-documented... this is a last resort... I think this may
Inevitably be the route I need to take.|||Different logical partitions on the same physical array do not give any
performance benefits. When you do a resource intensive operation such as a
reindex the data and log activities will contend with each other on the same
physical array. One thing you can do to minimize this effect and to speed up
the overall operation is to put the database in Simple recovery mode so the
index rebuilds will be done with a minimally logged operation. That will
minimize the data going to the transaction logs. Make sure to have a valid
full backup before you start, change the recovery mode to Simple and then
rebuild all the indexes one table at a time with DBCC
DBREINDEX('TableName'). A 50GB db with a 4 processor system should only
take a few hours even with poor disks. Make sure the MAXDOP of the server is
set to 0 or 4 so you get maximum parallelism when rebuilding. I would test
this first on a copy of the db just to ensure you have it all down right.
Then change the recovery mode back to Full (if that's what you had before)
and make to take another FULL backup when done.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Curtis" <curtmorrison@.yahoo.com> wrote in message
news:313d59e5-33d9-4161-8d4c-1f7fa2b91c27@.v4g2000hsf.googlegroups.com...
> On Nov 21, 3:50 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
>> First off I would highly recommend you DONT shrink the files.
> After giving this some more consideration, I agree...
>> What is your current disk setup like?
> It looks like the data and logs are on different partitions on a SAN.
> I don't have any additional seperate physical partitions to play with.
>> But if you want to defrag everything you may get faster results by
>> BCP'ing out all the
>> data in each table, truncating the tables and BCP or Bulk Inserting back
>> in
>> again.
> Exactly my thoughts in the first place, but the system is so complex
> and so un-documented... this is a last resort... I think this may
> Inevitably be the route I need to take.
>|||On Nov 22, 6:26 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
One thing you can do to minimize this effect and to speed up
> the overall operation is to put the database in Simple recovery mode so the
> index rebuilds will be done with a minimally logged operation. That will
> minimize the data going to the transaction logs.
Thanks Andrew - Your thought are exactly on track with what I have
already tried.|||This is one of my favorite scenarios. We have the same problem almost except
we carry tables with 400mill rows and our window is smaller than yours.
What we end up doing is almost the same thing. We do a select into another
table, and create all the indexes on that new table. Then we just rename the
original table to OLD and then the new table gets the original name... did I
say that right?
1. select * into customerNEW from customer.
2. Build indexes on customerNEW.
3. rename customer to customerOLD.
4. rename customerNEW to customer.
5. Drop table customerOLD.
and you're done...
Of course, if you're lucky enough to be on Yukon, then you can partition and
reindex a single partition at a time and get much better results. That is if
you don't need the parallel reads that weren't added. However, you can get
concurrency back if you put a partitioned view on top of them. I know that's
a workaround for it, but it's all we've got until katmai.
"Curtis" wrote:
> Hi,
> I have a 50GB database on SQL Server 2000 Enterprise that is highly
> transactional in a 24x7 environment. There's approximately 8 GB of
> free space, so I want to shrink it. It has been online for over five
> years and has never been defraged or reorganized. I've been given a
> maximum downtime window of eight hours. A test defrag on a copy of the
> database ran for almost two days (single user mode, quad-cpu server).
> I can't risk deadlocks with this system (lots of foreign key
> contraints, etc.), so I am unable to run the defrag while it is
> online.
> I'm wondering if moving all of the data to a new file in the same
> filegroup would be faster and also eliminate the existing
> fragmentation and possibly truncate the free space. Does anyone have
> any suggestions?
> Thanks!
>

Optimizing and shrinking large highly-transactional database

Hi,
I have a 50GB database on SQL Server 2000 Enterprise that is highly
transactional in a 24x7 environment. There's approximately 8 GB of
free space, so I want to shrink it. It has been online for over five
years and has never been defraged or reorganized. I've been given a
maximum downtime window of eight hours. A test defrag on a copy of the
database ran for almost two days (single user mode, quad-cpu server).
I can't risk deadlocks with this system (lots of foreign key
contraints, etc.), so I am unable to run the defrag while it is
online.
I'm wondering if moving all of the data to a new file in the same
filegroup would be faster and also eliminate the existing
fragmentation and possibly truncate the free space. Does anyone have
any suggestions?
Thanks!First off I would highly recommend you DONT shrink the files. 8GB of free
space in a 50GB db is the minimum free space I would like to see. You need
free space to minimize fragmentation and for operations such as reindexing
to work properly. See here for more details:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
But two days to rebuild less than 50GB of indexes is pretty pathetic and I
suspect something else is wrong. Make sure the log files for the user and
tempdb databases are on a separate raid array than the data and preferably
on a Raid 1 or Raid 10. What is your current disk setup like? But if you
want to defrag everything you may get faster results by BCP'ing out all the
data in each table, truncating the tables and BCP or Bulk Inserting back in
again. I would drop all the non-clustered indexes before importing back in
and re add them afterwards. I would also use an Order by clause when
exporting to export them in the order of the clustered index to give the
best chance of a clean and fast import for the clustered index. If you
export the data to a disk other than the one the data or log files are on
this should be a relatively quick process. But again even if you simply
reindexed each table one at a time with 4 processors it should only take a
short while.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Curtis" <curtmorrison@.yahoo.com> wrote in message
news:1d585c72-be6c-4913-a18d-5e38c6aa1bcb@.w34g2000hsg.googlegroups.com...
> Hi,
> I have a 50GB database on SQL Server 2000 Enterprise that is highly
> transactional in a 24x7 environment. There's approximately 8 GB of
> free space, so I want to shrink it. It has been online for over five
> years and has never been defraged or reorganized. I've been given a
> maximum downtime window of eight hours. A test defrag on a copy of the
> database ran for almost two days (single user mode, quad-cpu server).
> I can't risk deadlocks with this system (lots of foreign key
> contraints, etc.), so I am unable to run the defrag while it is
> online.
> I'm wondering if moving all of the data to a new file in the same
> filegroup would be faster and also eliminate the existing
> fragmentation and possibly truncate the free space. Does anyone have
> any suggestions?
> Thanks!|||On Nov 21, 3:50 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> First off I would highly recommend you DONT shrink the files.
After giving this some more consideration, I agree...

> What is your current disk setup like?
It looks like the data and logs are on different partitions on a SAN.
I don't have any additional seperate physical partitions to play with.

> But if you want to defrag everything you may get faster results by BCP'ing
out all the
> data in each table, truncating the tables and BCP or Bulk Inserting back i
n
> again.
Exactly my thoughts in the first place, but the system is so complex
and so un-documented... this is a last resort... I think this may
Inevitably be the route I need to take.|||Different logical partitions on the same physical array do not give any
performance benefits. When you do a resource intensive operation such as a
reindex the data and log activities will contend with each other on the same
physical array. One thing you can do to minimize this effect and to speed up
the overall operation is to put the database in Simple recovery mode so the
index rebuilds will be done with a minimally logged operation. That will
minimize the data going to the transaction logs. Make sure to have a valid
full backup before you start, change the recovery mode to Simple and then
rebuild all the indexes one table at a time with DBCC
DBREINDEX('TableName'). A 50GB db with a 4 processor system should only
take a few hours even with poor disks. Make sure the MAXDOP of the server is
set to 0 or 4 so you get maximum parallelism when rebuilding. I would test
this first on a copy of the db just to ensure you have it all down right.
Then change the recovery mode back to Full (if that's what you had before)
and make to take another FULL backup when done.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Curtis" <curtmorrison@.yahoo.com> wrote in message
news:313d59e5-33d9-4161-8d4c-1f7fa2b91c27@.v4g2000hsf.googlegroups.com...
> On Nov 21, 3:50 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
> wrote:
> After giving this some more consideration, I agree...
>
> It looks like the data and logs are on different partitions on a SAN.
> I don't have any additional seperate physical partitions to play with.
>
> Exactly my thoughts in the first place, but the system is so complex
> and so un-documented... this is a last resort... I think this may
> Inevitably be the route I need to take.
>|||On Nov 22, 6:26 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
One thing you can do to minimize this effect and to speed up
> the overall operation is to put the database in Simple recovery mode so th
e
> index rebuilds will be done with a minimally logged operation. That will
> minimize the data going to the transaction logs.
Thanks Andrew - Your thought are exactly on track with what I have
already tried.|||This is one of my favorite scenarios. We have the same problem almost excep
t
we carry tables with 400mill rows and our window is smaller than yours.
What we end up doing is almost the same thing. We do a select into another
table, and create all the indexes on that new table. Then we just rename th
e
original table to OLD and then the new table gets the original name... did I
say that right?
1. select * into customerNEW from customer.
2. Build indexes on customerNEW.
3. rename customer to customerOLD.
4. rename customerNEW to customer.
5. Drop table customerOLD.
and you're done...
Of course, if you're lucky enough to be on Yukon, then you can partition and
reindex a single partition at a time and get much better results. That is i
f
you don't need the parallel reads that weren't added. However, you can get
concurrency back if you put a partitioned view on top of them. I know that'
s
a workaround for it, but it's all we've got until katmai.
"Curtis" wrote:

> Hi,
> I have a 50GB database on SQL Server 2000 Enterprise that is highly
> transactional in a 24x7 environment. There's approximately 8 GB of
> free space, so I want to shrink it. It has been online for over five
> years and has never been defraged or reorganized. I've been given a
> maximum downtime window of eight hours. A test defrag on a copy of the
> database ran for almost two days (single user mode, quad-cpu server).
> I can't risk deadlocks with this system (lots of foreign key
> contraints, etc.), so I am unable to run the defrag while it is
> online.
> I'm wondering if moving all of the data to a new file in the same
> filegroup would be faster and also eliminate the existing
> fragmentation and possibly truncate the free space. Does anyone have
> any suggestions?
> Thanks!
>

Optimization Plan Grows a Database?

I have inherited a database from another site. It starts
out at approximately 10GB for the data file. If I run a
maintenance plan with the default settings for the
Optimizations the data file grows from 10GB to 17GB GB and
the log file grows from 255MB to 6 GB.
Anyone have any explanation why this grows so big?
I assume the maintenance reindexes the databases (reorganize data and index
pages on the second tab of the maintenance plan wizard) and the database
runs under the full recovery model, so all the reindexing operations are
logged.
Jacco Schalkwijk
SQL Server MVP
"Bill Holland" <hollandwl@.state.gov> wrote in message
news:2e9801c45dc0$d96f4ab0$3a01280a@.phx.gbl...
> I have inherited a database from another site. It starts
> out at approximately 10GB for the data file. If I run a
> maintenance plan with the default settings for the
> Optimizations the data file grows from 10GB to 17GB GB and
> the log file grows from 255MB to 6 GB.
> Anyone have any explanation why this grows so big?
|||I assume the maintenance reindexes the databases (reorganize data and index
pages on the second tab of the maintenance plan wizard) and the database
runs under the full recovery model, so all the reindexing operations are
logged.
Jacco Schalkwijk
SQL Server MVP
"Bill Holland" <hollandwl@.state.gov> wrote in message
news:2e9801c45dc0$d96f4ab0$3a01280a@.phx.gbl...
> I have inherited a database from another site. It starts
> out at approximately 10GB for the data file. If I run a
> maintenance plan with the default settings for the
> Optimizations the data file grows from 10GB to 17GB GB and
> the log file grows from 255MB to 6 GB.
> Anyone have any explanation why this grows so big?
|||The Optimization runs a DBREINDEX which is logged.
Another option is to consider DBCC INDEXDEFRAG. However, you
don't need to defrag indexes unless they are fragmented to
start with. The updated Books Online DBCC SHOWCONTIG topic
has a script which will defrag only where you actually have
some fragmentation.
You should read the following article for guidelines on
defragging:
http://www.microsoft.com/technet/pro...ss2kidbp.mspx.
-Sue
On Tue, 29 Jun 2004 03:07:13 -0700, "Bill Holland"
<hollandwl@.state.gov> wrote:

>I have inherited a database from another site. It starts
>out at approximately 10GB for the data file. If I run a
>maintenance plan with the default settings for the
>Optimizations the data file grows from 10GB to 17GB GB and
>the log file grows from 255MB to 6 GB.
>Anyone have any explanation why this grows so big?
|||The Optimization runs a DBREINDEX which is logged.
Another option is to consider DBCC INDEXDEFRAG. However, you
don't need to defrag indexes unless they are fragmented to
start with. The updated Books Online DBCC SHOWCONTIG topic
has a script which will defrag only where you actually have
some fragmentation.
You should read the following article for guidelines on
defragging:
http://www.microsoft.com/technet/pro...ss2kidbp.mspx.
-Sue
On Tue, 29 Jun 2004 03:07:13 -0700, "Bill Holland"
<hollandwl@.state.gov> wrote:

>I have inherited a database from another site. It starts
>out at approximately 10GB for the data file. If I run a
>maintenance plan with the default settings for the
>Optimizations the data file grows from 10GB to 17GB GB and
>the log file grows from 255MB to 6 GB.
>Anyone have any explanation why this grows so big?

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