Reorg indexes & remove unused space - known problem??

Using the DB Maint Wizard, we set up maint jobs to reorg indexes and remove unused space. But the database files are allocated twice as large as the required data afterwards (i.e. allocated at 10G but only using 5G). My theory is that the shrink either
doesn't work or it is happening before the rebuild of the indexes. And the additional space is being allocated to be used temporarily to rebuild the indexes but not released back to the OS.
My question is: Is Microsoft doing these 2 things in the wrong order or does the remove unused space option simply not work when reindexing? And, more importantly, is this a known issue? If so, what number? How do they recommend we get around this?
Stephanie,
These two steps are scheduled independently. Just check the sequence for the two steps and you will
see what order they are scheduled. If you want to change that order, just change the schedule for
the jobs. Also, I don't recommend regular shrinking, see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:E86B9392-3ACF-429D-842B-A91BF1F42787@.microsoft.com...
> Using the DB Maint Wizard, we set up maint jobs to reorg indexes and remove unused space. But the
database files are allocated twice as large as the required data afterwards (i.e. allocated at 10G
but only using 5G). My theory is that the shrink either doesn't work or it is happening before the
rebuild of the indexes. And the additional space is being allocated to be used temporarily to
rebuild the indexes but not released back to the OS.
> My question is: Is Microsoft doing these 2 things in the wrong order or does the remove unused
space option simply not work when reindexing? And, more importantly, is this a known issue? If so,
what number? How do they recommend we get around this?
|||Stephanie,
These two steps are scheduled independently. Just check the sequence for the two steps and you will
see what order they are scheduled. If you want to change that order, just change the schedule for
the jobs. Also, I don't recommend regular shrinking, see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:E86B9392-3ACF-429D-842B-A91BF1F42787@.microsoft.com...
> Using the DB Maint Wizard, we set up maint jobs to reorg indexes and remove unused space. But the
database files are allocated twice as large as the required data afterwards (i.e. allocated at 10G
but only using 5G). My theory is that the shrink either doesn't work or it is happening before the
rebuild of the indexes. And the additional space is being allocated to be used temporarily to
rebuild the indexes but not released back to the OS.
> My question is: Is Microsoft doing these 2 things in the wrong order or does the remove unused
space option simply not work when reindexing? And, more importantly, is this a known issue? If so,
what number? How do they recommend we get around this?
|||Tibor - Thanks for the response. I started out with a database with multiple data files that I shrunk to 1% free because some of the data files were 5G. Then I ran the maint job. Afterwards, I looked in the file that was logged from the maintenance job
and the reindex is happening before the removal of unused space. But I now have a database with about 50% fill in the data files. The time on the removal of unused space is 1 second for each database when one of the databases is 13G. The removal of un
used space cannot possibly be working successfully. Could it be possible that SQL Server is unaware of the additional space being allocated during the rebuild when it does the remove?
"Tibor Karaszi" wrote:

> Stephanie,
> These two steps are scheduled independently. Just check the sequence for the two steps and you will
> see what order they are scheduled. If you want to change that order, just change the schedule for
> the jobs. Also, I don't recommend regular shrinking, see:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:E86B9392-3ACF-429D-842B-A91BF1F42787@.microsoft.com...
> database files are allocated twice as large as the required data afterwards (i.e. allocated at 10G
> but only using 5G). My theory is that the shrink either doesn't work or it is happening before the
> rebuild of the indexes. And the additional space is being allocated to be used temporarily to
> rebuild the indexes but not released back to the OS.
> space option simply not work when reindexing? And, more importantly, is this a known issue? If so,
> what number? How do they recommend we get around this?
>
>
|||Tibor - Thanks for the response. I started out with a database with multiple data files that I shrunk to 1% free because some of the data files were 5G. Then I ran the maint job. Afterwards, I looked in the file that was logged from the maintenance job
and the reindex is happening before the removal of unused space. But I now have a database with about 50% fill in the data files. The time on the removal of unused space is 1 second for each database when one of the databases is 13G. The removal of un
used space cannot possibly be working successfully. Could it be possible that SQL Server is unaware of the additional space being allocated during the rebuild when it does the remove?
"Tibor Karaszi" wrote:

> Stephanie,
> These two steps are scheduled independently. Just check the sequence for the two steps and you will
> see what order they are scheduled. If you want to change that order, just change the schedule for
> the jobs. Also, I don't recommend regular shrinking, see:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:E86B9392-3ACF-429D-842B-A91BF1F42787@.microsoft.com...
> database files are allocated twice as large as the required data afterwards (i.e. allocated at 10G
> but only using 5G). My theory is that the shrink either doesn't work or it is happening before the
> rebuild of the indexes. And the additional space is being allocated to be used temporarily to
> rebuild the indexes but not released back to the OS.
> space option simply not work when reindexing? And, more importantly, is this a known issue? If so,
> what number? How do they recommend we get around this?
>
>
|||I suspect you're doing a TRUNCATEONLY shrink which doesn't move any data but
just releases any unallocated space at the end of the data file back to the
OS.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:CB411573-0C42-4DE0-8AD7-CF1E1852C154@.microsoft.com...
> Tibor - Thanks for the response. I started out with a database with
multiple data files that I shrunk to 1% free because some of the data files
were 5G. Then I ran the maint job. Afterwards, I looked in the file that
was logged from the maintenance job and the reindex is happening before the
removal of unused space. But I now have a database with about 50% fill in
the data files. The time on the removal of unused space is 1 second for
each database when one of the databases is 13G. The removal of unused space
cannot possibly be working successfully. Could it be possible that SQL
Server is unaware of the additional space being allocated during the rebuild
when it does the remove?[vbcol=seagreen]
> "Tibor Karaszi" wrote:
the two steps and you will[vbcol=seagreen]
just change the schedule for[vbcol=seagreen]
remove unused space. But the[vbcol=seagreen]
afterwards (i.e. allocated at 10G[vbcol=seagreen]
it is happening before the[vbcol=seagreen]
be used temporarily to[vbcol=seagreen]
or does the remove unused[vbcol=seagreen]
this a known issue? If so,[vbcol=seagreen]
|||When I issued my shrink 3 days ago because I saw how large the data files were, I used DBCC SHRINKDATABASE ('<dbname>', 1) to leave 1% free space, which it did just fine leaving me with a 5G data file with about 50M available among other data files. Grea
t! Not a lot of wasted space. I'm happy.
Then, I used the Database Maintenance Wizard and scheduled a job that rebuilt the indexes and removed unused space. As you know, it uses xp_sqlmaint with '-RmUnusedSpace 50 10' as a parameter to remove the unused space. What command it is actually issui
ng, (SHRINKDATABASE with TRUNCATEONLY, etc.) I do not know. After this job ran, I was back to having a 10G data file with only 5G used. Again, I'm assuming it needed the space for the rebuild of the indexes. But the Database Maintenance Wizard-based 'r
emove unused space' parameter did not help. What suggestions do you have? I want to rebuild indexes but I do not want twice as much space taken up afterwards as I need.
"Paul S Randal [MS]" wrote:

> I suspect you're doing a TRUNCATEONLY shrink which doesn't move any data but
> just releases any unallocated space at the end of the data file back to the
> OS.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:CB411573-0C42-4DE0-8AD7-CF1E1852C154@.microsoft.com...
> multiple data files that I shrunk to 1% free because some of the data files
> were 5G. Then I ran the maint job. Afterwards, I looked in the file that
> was logged from the maintenance job and the reindex is happening before the
> removal of unused space. But I now have a database with about 50% fill in
> the data files. The time on the removal of unused space is 1 second for
> each database when one of the databases is 13G. The removal of unused space
> cannot possibly be working successfully. Could it be possible that SQL
> Server is unaware of the additional space being allocated during the rebuild
> when it does the remove?
> the two steps and you will
> just change the schedule for
> remove unused space. But the
> afterwards (i.e. allocated at 10G
> it is happening before the
> be used temporarily to
> or does the remove unused
> this a known issue? If so,
>
>
|||Reindexing on a as needed basis is not a problem. But it is recommended you
have free space in the database at least 1.2 times the size of the table and
indexes (for that table) you are reindexing. Any less than that and you will
most assuredly auto grow the database. If you are even close to that little
free you will most likely not end up with tables that are contiguous on
disk. If this is a strictly OLTP operation that may not be a problem. If
your doing range scans it may. See here:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
But why do you care how much free space is in the files? If it needs more
than that and doesn't have it the files must grow. This is an expensive
process. The shrinking is even more expensive. Both of these affect user
performance. The other problem with keeping a minimum amount of file size
is that you may (or someone else) use that free space on the disk for
something else. Then when you do need it (for say a reindex) it won't be
there and your stuck. There is no penalty for having too much free space
but there is a big one for not enough. I am not saying you need twice the
amount but it has to be enough so that normal activities such as reindexing
and data loads do not autogrow the files. Also remember that SQL Server
inserts new data into files using a proportional fill algorythm that is
based on the amount of free space in the files. So if you have multiple
files in the filegroup and they are of different sizes or different % full
you may not get peak performance on writes or reads as you might if they
were spread evenly across the files.
One more comment. If you are going to shrink you should use DBCC SHRINKFILE
and not SHRINKDATABASE. The latter works on both the log and data files and
you don't have much say in how it breaks it all up.
Andrew J. Kelly SQL MVP
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:23D1318B-9F67-49A6-BD4C-5387D2468ACA@.microsoft.com...
> The bottom line is that I need to reindex on an as needed basis. I also
do not want more than 10% space available in these large data files. Can
someone PLEASE give me some specific positive feedback on best practices for
achieving this goal? I would really appreciate it.[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
remove[vbcol=seagreen]
the[vbcol=seagreen]
what[vbcol=seagreen]
there[vbcol=seagreen]
files[vbcol=seagreen]
I'm[vbcol=seagreen]
unused[vbcol=seagreen]
having[vbcol=seagreen]
space[vbcol=seagreen]
Wizard-based[vbcol=seagreen]
have?[vbcol=seagreen]
data[vbcol=seagreen]
to[vbcol=seagreen]
with[vbcol=seagreen]
data[vbcol=seagreen]
file[vbcol=seagreen]
before[vbcol=seagreen]
fill[vbcol=seagreen]
for[vbcol=seagreen]
unused[vbcol=seagreen]
SQL[vbcol=seagreen]
sequence[vbcol=seagreen]
order,[vbcol=seagreen]
message[vbcol=seagreen]
indexes[vbcol=seagreen]
allocated[vbcol=seagreen]
|||Okay, all good points. Thank you for taking the time to answer. Let's say I need to come up with a single automated maintenance plan for multpile environments, multiple servers and multiple OLTP databases that's easy to implement. I need to incorporate
reindexing on a regular basis. I also need to keep the database and log files at a good capacity (i.e. 20% free space). I was going to use a single Database Maintenance Plan but that doesn't seem to be working for me since the free space after a dbcc r
eindex is close to 50%. What would you suggest?
Right now I have 3 DB Maint Plan jobs running on all databases: one to check db integrity on Saturday evening, one to rebuild the indexes early on Sunday, and one to remove unused space (mostly from the reindex) keeping 20% that runs Sunday thru Thursday
nights.
My concern is that people restore databases to some of the development servers that have 50% free space. We don't have room for multiple copies of these databases so I wanted to perform some shrinks on a regular basis, 20% is better.
Could you please give me some suggestions for improvement of this design?
"Andrew J. Kelly" wrote:

> Reindexing on a as needed basis is not a problem. But it is recommended you
> have free space in the database at least 1.2 times the size of the table and
> indexes (for that table) you are reindexing. Any less than that and you will
> most assuredly auto grow the database. If you are even close to that little
> free you will most likely not end up with tables that are contiguous on
> disk. If this is a strictly OLTP operation that may not be a problem. If
> your doing range scans it may. See here:
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> But why do you care how much free space is in the files? If it needs more
> than that and doesn't have it the files must grow. This is an expensive
> process. The shrinking is even more expensive. Both of these affect user
> performance. The other problem with keeping a minimum amount of file size
> is that you may (or someone else) use that free space on the disk for
> something else. Then when you do need it (for say a reindex) it won't be
> there and your stuck. There is no penalty for having too much free space
> but there is a big one for not enough. I am not saying you need twice the
> amount but it has to be enough so that normal activities such as reindexing
> and data loads do not autogrow the files. Also remember that SQL Server
> inserts new data into files using a proportional fill algorythm that is
> based on the amount of free space in the files. So if you have multiple
> files in the filegroup and they are of different sizes or different % full
> you may not get peak performance on writes or reads as you might if they
> were spread evenly across the files.
> One more comment. If you are going to shrink you should use DBCC SHRINKFILE
> and not SHRINKDATABASE. The latter works on both the log and data files and
> you don't have much say in how it breaks it all up.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:23D1318B-9F67-49A6-BD4C-5387D2468ACA@.microsoft.com...
> do not want more than 10% space available in these large data files. Can
> someone PLEASE give me some specific positive feedback on best practices for
> achieving this goal? I would really appreciate it.
> remove
> the
> what
> there
> files
> I'm
> unused
> having
> space
> Wizard-based
> have?
> data
> to
> with
> data
> file
> before
> fill
> for
> unused
> SQL
> sequence
> order,
> message
> indexes
> allocated
>
>
|||Stephanie,
I don't recommend using a maintenance plan created via the wizard. I
recommend you create your own scheduled jobs and that way you can customize
it any way you see fit. The wizard uses SHRINKDATABASE and you should be
using SHRINK FILE so you can control it better. That way you can shrink the
log separate from the data files. But if it keeps giving you 50% free space
after a reindex then the amount of free space before the reindex is too
small. Part of this is probably due to the fact it grows multiple times and
you probably have the autogrow set to 10%. If these are large files then
maybe setting a size in MB is better. 10% of 50GB is a 5GB growth. But it
all boils down to not having enough before you start the process. Even if
you manually grew the files before you started the reindex the shrinking
process will destroy a lot of what you just accomplished. It's almost a
wasted effort. Maybe you can try DBCC INDEXDEFRAG instead. This may cause
more logging to the tran log but might keep the data files smaller. It's
MUCH easier to shrink the log file than the data file. Bottom line is that
your impacting the performance of a production server to accommodate
development servers. It's better to get more disk space on the devel
servers. Even if you get enough room to restore the a copy of the
production db and then shrink it on the devel server would be better than
the current way if possible.
Andrew J. Kelly SQL MVP
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:51F36354-285B-4E71-BAC7-E4BD44559CED@.microsoft.com...
> Okay, all good points. Thank you for taking the time to answer. Let's
say I need to come up with a single automated maintenance plan for multpile
environments, multiple servers and multiple OLTP databases that's easy to
implement. I need to incorporate reindexing on a regular basis. I also
need to keep the database and log files at a good capacity (i.e. 20% free
space). I was going to use a single Database Maintenance Plan but that
doesn't seem to be working for me since the free space after a dbcc reindex
is close to 50%. What would you suggest?
> Right now I have 3 DB Maint Plan jobs running on all databases: one to
check db integrity on Saturday evening, one to rebuild the indexes early on
Sunday, and one to remove unused space (mostly from the reindex) keeping 20%
that runs Sunday thru Thursday nights.
> My concern is that people restore databases to some of the development
servers that have 50% free space. We don't have room for multiple copies of
these databases so I wanted to perform some shrinks on a regular basis, 20%
is better.[vbcol=seagreen]
> Could you please give me some suggestions for improvement of this design?
> "Andrew J. Kelly" wrote:
you[vbcol=seagreen]
and[vbcol=seagreen]
will[vbcol=seagreen]
little[vbcol=seagreen]
If[vbcol=seagreen]
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
more[vbcol=seagreen]
user[vbcol=seagreen]
size[vbcol=seagreen]
be[vbcol=seagreen]
space[vbcol=seagreen]
the[vbcol=seagreen]
reindexing[vbcol=seagreen]
Server[vbcol=seagreen]
full[vbcol=seagreen]
SHRINKFILE[vbcol=seagreen]
and[vbcol=seagreen]
also[vbcol=seagreen]
Can[vbcol=seagreen]
for[vbcol=seagreen]
the[vbcol=seagreen]
grow[vbcol=seagreen]
at[vbcol=seagreen]
system[vbcol=seagreen]
data[vbcol=seagreen]
space,[vbcol=seagreen]
space.[vbcol=seagreen]
that[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
you[vbcol=seagreen]
taken up[vbcol=seagreen]
any[vbcol=seagreen]
back[vbcol=seagreen]
no[vbcol=seagreen]
message[vbcol=seagreen]
database[vbcol=seagreen]
50%[vbcol=seagreen]
second[vbcol=seagreen]
that[vbcol=seagreen]
the[vbcol=seagreen]
that[vbcol=seagreen]
data[vbcol=seagreen]
doesn't[vbcol=seagreen]
wrong[vbcol=seagreen]