Re: Free and Used Allocated Space with Shrinkfile

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Don (anonymous_at_discussions.microsoft.com)
Date: 01/24/05 

Date: Mon, 24 Jan 2005 10:36:03 -0800

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
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>