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...
>> 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
>>
>
>.
>|||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...
> 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...
> >> 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 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...
> 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...
>> 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
>>
>>
>>.|||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...
>> 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
>>
>
>.
>|||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...
>> 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...
>> >> 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
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||> 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.
>--Original Message--
>> 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).|||What a jerk...
>--Original Message--
>> 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).
>
>.
>|||This is the kind of immature crap that makes me wonder why I bother to help
anyone at all.
"Oh geez, you're right, but I don't like your tone. So instead of thanking
you and taking your advice, I'm going to call you names!"
*plonk*
> What a jerk...|||Yes Don it will but you obviously needed more space than one growth block
gave you. Once it used up that amount it needed to grow again. In your
case it must have grown several times. You can trace the Autogrow event if
you want to see when it grows and how many times. This is very common with
database maintenance activities.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:21b901c50243$66ce07d0$a401280a@.phx.gbl...
>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...
>> 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
>>
>>
>>.