Free and Used Allocated Space with Shrinkfile

SQL 7.0 NT 2000 server
Before doing anything:
Used: Free: Total
20GB 20GB 40GB
After
dbcc shrinkfile(1,notruncate)
followed by
dbcc shrinkfile(1,truncateonly)
the view from EM is:
Used: Free: Total
20GB 1.6MB 21GB
Within 24 hours
Used: Free: Total
20GB 12GB 32GB
The db and trans log are set to grow automatically by 10%
so how can the MDF file suddenly go to 32GB? Shouldn't
SQL expand the 1.6MB by 10% when needed, and then 10%
after that, etc?
There have been no deletions in the DB only insertions.
Thanks for your help.
DonThe growth is most likely due to a reindexing job (probably a maintenance
plan) at night. The growth is 10% of the entire file and not the 1.6MB
free. So the first growth would be 2GB and it just gets larger from there.
When you have a db over a few GB it is a good idea to change the growth
increment to a fixed amount and not a percentage so it always grows in a
controllable fashion. But never shrink an active database down to anywhere
near the size of the data. The db needs lots of free space to work
properly.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:246501c50239$11fd94f0$a601280a@.phx.gbl...
> SQL 7.0 NT 2000 server
> Before doing anything:
> Used: Free: Total
> 20GB 20GB 40GB
> After
> dbcc shrinkfile(1,notruncate)
> followed by
> dbcc shrinkfile(1,truncateonly)
> the view from EM is:
> Used: Free: Total
> 20GB 1.6MB 21GB
> Within 24 hours
> Used: Free: Total
> 20GB 12GB 32GB
> The db and trans log are set to grow automatically by 10%
> so how can the MDF file suddenly go to 32GB? Shouldn't
> SQL expand the 1.6MB by 10% when needed, and then 10%
> after that, etc?
> There have been no deletions in the DB only insertions.
> Thanks for your help.
> Don
>|||In addition to Andrew's comments, please read:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.aspfaq.com/
(Reverse address to reply.)
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:246501c50239$11fd94f0$a601280a@.phx.gbl...
> SQL 7.0 NT 2000 server
> Before doing anything:
> Used: Free: Total
> 20GB 20GB 40GB
> After
> dbcc shrinkfile(1,notruncate)
> followed by
> dbcc shrinkfile(1,truncateonly)
> the view from EM is:
> Used: Free: Total
> 20GB 1.6MB 21GB
> Within 24 hours
> Used: Free: Total
> 20GB 12GB 32GB
> The db and trans log are set to grow automatically by 10%
> so how can the MDF file suddenly go to 32GB? Shouldn't
> SQL expand the 1.6MB by 10% when needed, and then 10%
> after that, etc?
> There have been no deletions in the DB only insertions.
> Thanks for your help.
> Don
>|||Yes, there was an indexing maintenance job that ran.
As you say the first growth would be 2GB, but wouldn't
SQL use that space and then increase it by 10%?
Don

>--Original Message--
>The growth is most likely due to a reindexing job
(probably a maintenance
>plan) at night. The growth is 10% of the entire file
and not the 1.6MB
>free. So the first growth would be 2GB and it just gets
larger from there.
>When you have a db over a few GB it is a good idea to
change the growth
>increment to a fixed amount and not a percentage so it
always grows in a
>controllable fashion. But never shrink an active
database down to anywhere
>near the size of the data. The db needs lots of free
space to work
>properly.
>--
>Andrew J. Kelly SQL MVP
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
10%[vbcol=seagreen]
>
>.
>|||If you went from 20 GB to 32 GB, then my guess is it had to autogrow
MULTIPLE times.
http://www.aspfaq.com/
(Reverse address to reply.)
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:214101c5023c$620efd50$a401280a@.phx.gbl...[vbcol=seagreen]
> Yes, there was an indexing maintenance job that ran.
> As you say the first growth would be 2GB, but wouldn't
> SQL use that space and then increase it by 10%?
> Don
>
> (probably a maintenance
> and not the 1.6MB
> larger from there.
> change the growth
> always grows in a
> database down to anywhere
> space to work
> message
> 10%|||Yes as Aaron stated it had to have grown multiple times. Since a DBREINDEX
needs 1.2 (or more) times the size of the index in free space to rebuild it
this is not uncommon. If your db has 20 GB of actual data and indexes I see
no problem with have 10 or 20GB of free space in the data file. There is no
penalty for too much free space but a big one for too little.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:214101c5023c$620efd50$a401280a@.phx.gbl...[vbcol=seagreen]
> Yes, there was an indexing maintenance job that ran.
> As you say the first growth would be 2GB, but wouldn't
> SQL use that space and then increase it by 10%?
> Don
>
> (probably a maintenance
> and not the 1.6MB
> larger from there.
> change the growth
> always grows in a
> database down to anywhere
> space to work
> message
> 10%|||I seem to be missing the concept...
if I shrink a db down to the size of the data, and SQL
needs lots of free space to work with, won't SQL either
expand by a fixed amount or by percentage as needed?
Don

>--Original Message--
>The growth is most likely due to a reindexing job
(probably a maintenance
>plan) at night. The growth is 10% of the entire file
and not the 1.6MB
>free. So the first growth would be 2GB and it just gets
larger from there.
>When you have a db over a few GB it is a good idea to
change the growth
>increment to a fixed amount and not a percentage so it
always grows in a
>controllable fashion. But never shrink an active
database down to anywhere
>near the size of the data. The db needs lots of free
space to work
>properly.
>--
>Andrew J. Kelly SQL MVP
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
10%[vbcol=seagreen]
>
>.
>|||But, that's my question...
why would it expand MULTIPLE times? It's only supposed to
expand as needed...unless I'm missing something entirely.

>--Original Message--
>If you went from 20 GB to 32 GB, then my guess is it had
to autogrow
>MULTIPLE times.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:214101c5023c$620efd50$a401280a@.phx.gbl...
gets[vbcol=seagreen]
by[vbcol=seagreen]
Shouldn't[vbcol=seagreen]
10%[vbcol=seagreen]
insertions.[vbcol=seagreen]
>
>.
>|||> why would it expand MULTIPLE times? It's only supposed to
> expand as needed...unless I'm missing something entirely.
Who knows what your maintenance plan did, and what other activity is going
on in the box?
As for how many times it will expand, if you set it to 10%, and it needs a
GB, then another GB, then another GB, then another GB, it is going to grow
multiple times. Since we have ABSOLUTELY no idea what was going on in your
system, I think it's impossible for us to tell you how many times the file
needed to grow and why.
A|||> Seems to me that the LOG file is supposed to be used for
> these types of things.
Seems you should get a better grasp on what you are doing in your
maintenance plan. For example, if you reindex a table, it needs to allocate
data to other areas within the DATA file. Why would you think this should
happen exclusively in the LOG file?
If you don't have the ability or motivation to understand why you need more
disk space in this case, buy a bigger drive and stop shrinking your database
for no reason (and without understanding the causes/consequences).