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
>>
>>
>>.
Showing posts with label allocated. Show all posts
Showing posts with label allocated. Show all posts
Friday, February 24, 2012
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.
Don
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
>
|||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[vbcol=seagreen]
>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
10%
>
>.
>
|||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[vbcol=seagreen]
>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
10%
>
>.
>
|||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[vbcol=seagreen]
>news:214101c5023c$620efd50$a401280a@.phx.gbl...
gets[vbcol=seagreen]
by[vbcol=seagreen]
Shouldn't[vbcol=seagreen]
10%[vbcol=seagreen]
insertions.
>
>.
>
|||> 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).
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
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
>
|||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[vbcol=seagreen]
>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
10%
>
>.
>
|||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[vbcol=seagreen]
>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
10%
>
>.
>
|||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[vbcol=seagreen]
>news:214101c5023c$620efd50$a401280a@.phx.gbl...
gets[vbcol=seagreen]
by[vbcol=seagreen]
Shouldn't[vbcol=seagreen]
10%[vbcol=seagreen]
insertions.
>
>.
>
|||> 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).
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).
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).
Free "space allocated" is 0
We set our SQL Server database to "Automatically grow file" and
"Unrestricted file growth". Why in the TaskPad view, the free "space
allocated" is 0 ? Thank you.
The file has a certain size. For that size, it seems you have 0 free. The file can grow as space is
needed. Note that grow hurts performance (growing 100MB can take some 10 seconds during the user who
is victim for the grow sits and wait for the operation to be performed). I suggest you pre-allocate
storage so you have some free space, and of course not shrink the files regularly
(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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
> We set our SQL Server database to "Automatically grow file" and "Unrestricted file growth". Why in
> the TaskPad view, the free "space allocated" is 0 ? Thank you.
>
|||Thank you for your reply.
By checking the "Unrestricted file growth" and "Automatically grow file" ,
doesn't it suppose to allocate the space automatically (instead of having to
pre-allocate the storage manually) ?
Thank you.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
> The file has a certain size. For that size, it seems you have 0 free. The
> file can grow as space is needed. Note that grow hurts performance
> (growing 100MB can take some 10 seconds during the user who is victim for
> the grow sits and wait for the operation to be performed). I suggest you
> pre-allocate storage so you have some free space, and of course not shrink
> the files regularly
> (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/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
>
|||Yes, but the users who need space during the grow need to wait for the grow to finish. Also, a lot
of grow will cause fragmentation at the file system level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:%237ldhWAfFHA.1036@.tk2msftngp13.phx.gbl...
> Thank you for your reply.
> By checking the "Unrestricted file growth" and "Automatically grow file" , doesn't it suppose to
> allocate the space automatically (instead of having to pre-allocate the storage manually) ?
> Thank you.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
>
|||Thank you for your reply.
I am just wondering, why in my case even though "Unrestricted file growth"
and "Automatically grow file" are checked, it did not allocate the space
automatically ?
Is this the case like mentioned in your article
http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are
situations where autogrow doesn't "catch up" with the space usage
requirements" ?
So, the better way is to always manually allocate the space ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
> Yes, but the users who need space during the grow need to wait for the
> grow to finish. Also, a lot of grow will cause fragmentation at the file
> system level.
> --
|||Space isn't allocated automatically in advance. Imagine someone want to add an order, for instance.
An insert is performed by the application. The file is full so the file need to be expanded, while
the user waits.
And imagine if the grow takes long time, so the application does a time-out during this. The grow
will be rolled back and we are back to square zero. For medium to large databases, you want to
pre-allocate storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...
> Thank you for your reply.
> I am just wondering, why in my case even though "Unrestricted file growth" and "Automatically
> grow file" are checked, it did not allocate the space automatically ?
> Is this the case like mentioned in your article
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are situations where autogrow
> doesn't "catch up" with the space usage requirements" ?
> So, the better way is to always manually allocate the space ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
>
|||I see. In my case the database is about 2 gig, so when someone want to add a
new record, the grow takes a long time, and it times out, so it did not
allocate the space.
The application did get the "Timeout expired" error.
I will pre-allocate the space from now on.
Thanks a lot for your help. I appreciate it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTIhk9AfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Space isn't allocated automatically in advance. Imagine someone want to
> add an order, for instance. An insert is performed by the application. The
> file is full so the file need to be expanded, while the user waits.
> And imagine if the grow takes long time, so the application does a
> time-out during this. The grow will be rolled back and we are back to
> square zero. For medium to large databases, you want to pre-allocate
> storage.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...
>
|||One more thing.
I see there is a wizard to create an alert. If I want to create an alert
(right click on the database, View - Taskpad, tab "Wizards" - Create an
Alert) when it is time for me to pre-allocate space again, what "error
severity" shall I select from the wizard drop down box ?
Thanks.
|||Alerts are either based on perfmon counters. There's no perfmon counter for free space in a database
data file, but there is for a database tlog file. Or they are based on messages to eventlog. But
messages from SQL server are not written to eventlog when a database ix X percent full.
You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db" procedure. I suggest you
read the code and adapt it to your needs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
> One more thing.
> I see there is a wizard to create an alert. If I want to create an alert (right click on the
> database, View - Taskpad, tab "Wizards" - Create an Alert) when it is time for me to pre-allocate
> space again, what "error severity" shall I select from the wizard drop down box ?
> Thanks.
>
|||Thanks a lot for your help.
When you said "perfmon counters" did you mean performance counters ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ux5vfwHfFHA.1044@.tk2msftngp13.phx.gbl...
> Alerts are either based on perfmon counters. There's no perfmon counter
> for free space in a database data file, but there is for a database tlog
> file. Or they are based on messages to eventlog. But messages from SQL
> server are not written to eventlog when a database ix X percent full.
> You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db"
> procedure. I suggest you read the code and adapt it to your needs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
>
"Unrestricted file growth". Why in the TaskPad view, the free "space
allocated" is 0 ? Thank you.
The file has a certain size. For that size, it seems you have 0 free. The file can grow as space is
needed. Note that grow hurts performance (growing 100MB can take some 10 seconds during the user who
is victim for the grow sits and wait for the operation to be performed). I suggest you pre-allocate
storage so you have some free space, and of course not shrink the files regularly
(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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
> We set our SQL Server database to "Automatically grow file" and "Unrestricted file growth". Why in
> the TaskPad view, the free "space allocated" is 0 ? Thank you.
>
|||Thank you for your reply.
By checking the "Unrestricted file growth" and "Automatically grow file" ,
doesn't it suppose to allocate the space automatically (instead of having to
pre-allocate the storage manually) ?
Thank you.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
> The file has a certain size. For that size, it seems you have 0 free. The
> file can grow as space is needed. Note that grow hurts performance
> (growing 100MB can take some 10 seconds during the user who is victim for
> the grow sits and wait for the operation to be performed). I suggest you
> pre-allocate storage so you have some free space, and of course not shrink
> the files regularly
> (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/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
>
|||Yes, but the users who need space during the grow need to wait for the grow to finish. Also, a lot
of grow will cause fragmentation at the file system level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:%237ldhWAfFHA.1036@.tk2msftngp13.phx.gbl...
> Thank you for your reply.
> By checking the "Unrestricted file growth" and "Automatically grow file" , doesn't it suppose to
> allocate the space automatically (instead of having to pre-allocate the storage manually) ?
> Thank you.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
>
|||Thank you for your reply.
I am just wondering, why in my case even though "Unrestricted file growth"
and "Automatically grow file" are checked, it did not allocate the space
automatically ?
Is this the case like mentioned in your article
http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are
situations where autogrow doesn't "catch up" with the space usage
requirements" ?
So, the better way is to always manually allocate the space ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
> Yes, but the users who need space during the grow need to wait for the
> grow to finish. Also, a lot of grow will cause fragmentation at the file
> system level.
> --
|||Space isn't allocated automatically in advance. Imagine someone want to add an order, for instance.
An insert is performed by the application. The file is full so the file need to be expanded, while
the user waits.
And imagine if the grow takes long time, so the application does a time-out during this. The grow
will be rolled back and we are back to square zero. For medium to large databases, you want to
pre-allocate storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...
> Thank you for your reply.
> I am just wondering, why in my case even though "Unrestricted file growth" and "Automatically
> grow file" are checked, it did not allocate the space automatically ?
> Is this the case like mentioned in your article
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are situations where autogrow
> doesn't "catch up" with the space usage requirements" ?
> So, the better way is to always manually allocate the space ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
>
|||I see. In my case the database is about 2 gig, so when someone want to add a
new record, the grow takes a long time, and it times out, so it did not
allocate the space.
The application did get the "Timeout expired" error.
I will pre-allocate the space from now on.
Thanks a lot for your help. I appreciate it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTIhk9AfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Space isn't allocated automatically in advance. Imagine someone want to
> add an order, for instance. An insert is performed by the application. The
> file is full so the file need to be expanded, while the user waits.
> And imagine if the grow takes long time, so the application does a
> time-out during this. The grow will be rolled back and we are back to
> square zero. For medium to large databases, you want to pre-allocate
> storage.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...
>
|||One more thing.
I see there is a wizard to create an alert. If I want to create an alert
(right click on the database, View - Taskpad, tab "Wizards" - Create an
Alert) when it is time for me to pre-allocate space again, what "error
severity" shall I select from the wizard drop down box ?
Thanks.
|||Alerts are either based on perfmon counters. There's no perfmon counter for free space in a database
data file, but there is for a database tlog file. Or they are based on messages to eventlog. But
messages from SQL server are not written to eventlog when a database ix X percent full.
You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db" procedure. I suggest you
read the code and adapt it to your needs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
> One more thing.
> I see there is a wizard to create an alert. If I want to create an alert (right click on the
> database, View - Taskpad, tab "Wizards" - Create an Alert) when it is time for me to pre-allocate
> space again, what "error severity" shall I select from the wizard drop down box ?
> Thanks.
>
|||Thanks a lot for your help.
When you said "perfmon counters" did you mean performance counters ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ux5vfwHfFHA.1044@.tk2msftngp13.phx.gbl...
> Alerts are either based on perfmon counters. There's no perfmon counter
> for free space in a database data file, but there is for a database tlog
> file. Or they are based on messages to eventlog. But messages from SQL
> server are not written to eventlog when a database ix X percent full.
> You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db"
> procedure. I suggest you read the code and adapt it to your needs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
>
Free "space allocated" is 0
We set our SQL Server database to "Automatically grow file" and
"Unrestricted file growth". Why in the TaskPad view, the free "space
allocated" is 0 ? Thank you.The file has a certain size. For that size, it seems you have 0 free. The fi
le can grow as space is
needed. Note that grow hurts performance (growing 100MB can take some 10 sec
onds during the user who
is victim for the grow sits and wait for the operation to be performed). I s
uggest you pre-allocate
storage so you have some free space, and of course not shrink the files regu
larly
(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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl..
.
> We set our SQL Server database to "Automatically grow file" and "Unrestric
ted file growth". Why in
> the TaskPad view, the free "space allocated" is 0 ? Thank you.
>|||Thank you for your reply.
By checking the "Unrestricted file growth" and "Automatically grow file" ,
doesn't it suppose to allocate the space automatically (instead of having to
pre-allocate the storage manually) ?
Thank you.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
> The file has a certain size. For that size, it seems you have 0 free. The
> file can grow as space is needed. Note that grow hurts performance
> (growing 100MB can take some 10 seconds during the user who is victim for
> the grow sits and wait for the operation to be performed). I suggest you
> pre-allocate storage so you have some free space, and of course not shrink
> the files regularly
> (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/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
>|||Yes, but the users who need space during the grow need to wait for the grow
to finish. Also, a lot
of grow will cause fragmentation at the file system level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:%237ldhWAfFHA.1036@.tk2msftngp13.phx.gbl..
.
> Thank you for your reply.
> By checking the "Unrestricted file growth" and "Automatically grow file"
, doesn't it suppose to
> allocate the space automatically (instead of having to pre-allocate the st
orage manually) ?
> Thank you.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
>|||Thank you for your reply.
I am just wondering, why in my case even though "Unrestricted file growth"
and "Automatically grow file" are checked, it did not allocate the space
automatically ?
Is this the case like mentioned in your article
http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are
situations where autogrow doesn't "catch up" with the space usage
requirements" ?
So, the better way is to always manually allocate the space ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
> Yes, but the users who need space during the grow need to wait for the
> grow to finish. Also, a lot of grow will cause fragmentation at the file
> system level.
> --|||Space isn't allocated automatically in advance. Imagine someone want to add
an order, for instance.
An insert is performed by the application. The file is full so the file need
to be expanded, while
the user waits.
And imagine if the grow takes long time, so the application does a time-out
during this. The grow
will be rolled back and we are back to square zero. For medium to large data
bases, you want to
pre-allocate storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...[
vbcol=seagreen]
> Thank you for your reply.
> I am just wondering, why in my case even though "Unrestricted file growth"
and "Automatically
> grow file" are checked, it did not allocate the space automatically ?
> Is this the case like mentioned in your article
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are sit
uations where autogrow
> doesn't "catch up" with the space usage requirements" ?
> So, the better way is to always manually allocate the space ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
>[/vbcol]|||I see. In my case the database is about 2 gig, so when someone want to add a
new record, the grow takes a long time, and it times out, so it did not
allocate the space.
The application did get the "Timeout expired" error.
I will pre-allocate the space from now on.
Thanks a lot for your help. I appreciate it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTIhk9AfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Space isn't allocated automatically in advance. Imagine someone want to
> add an order, for instance. An insert is performed by the application. The
> file is full so the file need to be expanded, while the user waits.
> And imagine if the grow takes long time, so the application does a
> time-out during this. The grow will be rolled back and we are back to
> square zero. For medium to large databases, you want to pre-allocate
> storage.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...
>|||One more thing.
I see there is a wizard to create an alert. If I want to create an alert
(right click on the database, View - Taskpad, tab "Wizards" - Create an
Alert) when it is time for me to pre-allocate space again, what "error
severity" shall I select from the wizard drop down box ?
Thanks.|||Alerts are either based on perfmon counters. There's no perfmon counter for
free space in a database
data file, but there is for a database tlog file. Or they are based on messa
ges to eventlog. But
messages from SQL server are not written to eventlog when a database ix X pe
rcent full.
You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db" proc
edure. I suggest you
read the code and adapt it to your needs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...[
vbcol=seagreen]
> One more thing.
> I see there is a wizard to create an alert. If I want to create an alert (
right click on the
> database, View - Taskpad, tab "Wizards" - Create an Alert) when it is time
for me to pre-allocate
> space again, what "error severity" shall I select from the wizard drop dow
n box ?
> Thanks.
>[/vbcol]|||Thanks a lot for your help.
When you said "perfmon counters" did you mean performance counters ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ux5vfwHfFHA.1044@.tk2msftngp13.phx.gbl...
> Alerts are either based on perfmon counters. There's no perfmon counter
> for free space in a database data file, but there is for a database tlog
> file. Or they are based on messages to eventlog. But messages from SQL
> server are not written to eventlog when a database ix X percent full.
> You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db"
> procedure. I suggest you read the code and adapt it to your needs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
>
"Unrestricted file growth". Why in the TaskPad view, the free "space
allocated" is 0 ? Thank you.The file has a certain size. For that size, it seems you have 0 free. The fi
le can grow as space is
needed. Note that grow hurts performance (growing 100MB can take some 10 sec
onds during the user who
is victim for the grow sits and wait for the operation to be performed). I s
uggest you pre-allocate
storage so you have some free space, and of course not shrink the files regu
larly
(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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl..
.
> We set our SQL Server database to "Automatically grow file" and "Unrestric
ted file growth". Why in
> the TaskPad view, the free "space allocated" is 0 ? Thank you.
>|||Thank you for your reply.
By checking the "Unrestricted file growth" and "Automatically grow file" ,
doesn't it suppose to allocate the space automatically (instead of having to
pre-allocate the storage manually) ?
Thank you.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
> The file has a certain size. For that size, it seems you have 0 free. The
> file can grow as space is needed. Note that grow hurts performance
> (growing 100MB can take some 10 seconds during the user who is victim for
> the grow sits and wait for the operation to be performed). I suggest you
> pre-allocate storage so you have some free space, and of course not shrink
> the files regularly
> (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/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
>|||Yes, but the users who need space during the grow need to wait for the grow
to finish. Also, a lot
of grow will cause fragmentation at the file system level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:%237ldhWAfFHA.1036@.tk2msftngp13.phx.gbl..
.
> Thank you for your reply.
> By checking the "Unrestricted file growth" and "Automatically grow file"
, doesn't it suppose to
> allocate the space automatically (instead of having to pre-allocate the st
orage manually) ?
> Thank you.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
>|||Thank you for your reply.
I am just wondering, why in my case even though "Unrestricted file growth"
and "Automatically grow file" are checked, it did not allocate the space
automatically ?
Is this the case like mentioned in your article
http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are
situations where autogrow doesn't "catch up" with the space usage
requirements" ?
So, the better way is to always manually allocate the space ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
> Yes, but the users who need space during the grow need to wait for the
> grow to finish. Also, a lot of grow will cause fragmentation at the file
> system level.
> --|||Space isn't allocated automatically in advance. Imagine someone want to add
an order, for instance.
An insert is performed by the application. The file is full so the file need
to be expanded, while
the user waits.
And imagine if the grow takes long time, so the application does a time-out
during this. The grow
will be rolled back and we are back to square zero. For medium to large data
bases, you want to
pre-allocate storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...[
vbcol=seagreen]
> Thank you for your reply.
> I am just wondering, why in my case even though "Unrestricted file growth"
and "Automatically
> grow file" are checked, it did not allocate the space automatically ?
> Is this the case like mentioned in your article
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are sit
uations where autogrow
> doesn't "catch up" with the space usage requirements" ?
> So, the better way is to always manually allocate the space ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
>[/vbcol]|||I see. In my case the database is about 2 gig, so when someone want to add a
new record, the grow takes a long time, and it times out, so it did not
allocate the space.
The application did get the "Timeout expired" error.
I will pre-allocate the space from now on.
Thanks a lot for your help. I appreciate it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTIhk9AfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Space isn't allocated automatically in advance. Imagine someone want to
> add an order, for instance. An insert is performed by the application. The
> file is full so the file need to be expanded, while the user waits.
> And imagine if the grow takes long time, so the application does a
> time-out during this. The grow will be rolled back and we are back to
> square zero. For medium to large databases, you want to pre-allocate
> storage.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...
>|||One more thing.
I see there is a wizard to create an alert. If I want to create an alert
(right click on the database, View - Taskpad, tab "Wizards" - Create an
Alert) when it is time for me to pre-allocate space again, what "error
severity" shall I select from the wizard drop down box ?
Thanks.|||Alerts are either based on perfmon counters. There's no perfmon counter for
free space in a database
data file, but there is for a database tlog file. Or they are based on messa
ges to eventlog. But
messages from SQL server are not written to eventlog when a database ix X pe
rcent full.
You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db" proc
edure. I suggest you
read the code and adapt it to your needs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...[
vbcol=seagreen]
> One more thing.
> I see there is a wizard to create an alert. If I want to create an alert (
right click on the
> database, View - Taskpad, tab "Wizards" - Create an Alert) when it is time
for me to pre-allocate
> space again, what "error severity" shall I select from the wizard drop dow
n box ?
> Thanks.
>[/vbcol]|||Thanks a lot for your help.
When you said "perfmon counters" did you mean performance counters ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ux5vfwHfFHA.1044@.tk2msftngp13.phx.gbl...
> Alerts are either based on perfmon counters. There's no perfmon counter
> for free space in a database data file, but there is for a database tlog
> file. Or they are based on messages to eventlog. But messages from SQL
> server are not written to eventlog when a database ix X percent full.
> You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db"
> procedure. I suggest you read the code and adapt it to your needs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
>
Free "space allocated" is 0
We set our SQL Server database to "Automatically grow file" and
"Unrestricted file growth". Why in the TaskPad view, the free "space
allocated" is 0 ? Thank you.The file has a certain size. For that size, it seems you have 0 free. The file can grow as space is
needed. Note that grow hurts performance (growing 100MB can take some 10 seconds during the user who
is victim for the grow sits and wait for the operation to be performed). I suggest you pre-allocate
storage so you have some free space, and of course not shrink the files regularly
(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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
> We set our SQL Server database to "Automatically grow file" and "Unrestricted file growth". Why in
> the TaskPad view, the free "space allocated" is 0 ? Thank you.
>|||Thank you for your reply.
By checking the "Unrestricted file growth" and "Automatically grow file" ,
doesn't it suppose to allocate the space automatically (instead of having to
pre-allocate the storage manually) ?
Thank you.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
> The file has a certain size. For that size, it seems you have 0 free. The
> file can grow as space is needed. Note that grow hurts performance
> (growing 100MB can take some 10 seconds during the user who is victim for
> the grow sits and wait for the operation to be performed). I suggest you
> pre-allocate storage so you have some free space, and of course not shrink
> the files regularly
> (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/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> We set our SQL Server database to "Automatically grow file" and
>> "Unrestricted file growth". Why in the TaskPad view, the free "space
>> allocated" is 0 ? Thank you.
>|||Yes, but the users who need space during the grow need to wait for the grow to finish. Also, a lot
of grow will cause fragmentation at the file system level.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:%237ldhWAfFHA.1036@.tk2msftngp13.phx.gbl...
> Thank you for your reply.
> By checking the "Unrestricted file growth" and "Automatically grow file" , doesn't it suppose to
> allocate the space automatically (instead of having to pre-allocate the storage manually) ?
> Thank you.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
>> The file has a certain size. For that size, it seems you have 0 free. The file can grow as space
>> is needed. Note that grow hurts performance (growing 100MB can take some 10 seconds during the
>> user who is victim for the grow sits and wait for the operation to be performed). I suggest you
>> pre-allocate storage so you have some free space, and of course not shrink the files regularly
>> (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/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "fniles" <fniles@.pfmail.com> wrote in message news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> We set our SQL Server database to "Automatically grow file" and "Unrestricted file growth". Why
>> in the TaskPad view, the free "space allocated" is 0 ? Thank you.
>>
>|||Thank you for your reply.
I am just wondering, why in my case even though "Unrestricted file growth"
and "Automatically grow file" are checked, it did not allocate the space
automatically ?
Is this the case like mentioned in your article
http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are
situations where autogrow doesn't "catch up" with the space usage
requirements" ?
So, the better way is to always manually allocate the space ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
> Yes, but the users who need space during the grow need to wait for the
> grow to finish. Also, a lot of grow will cause fragmentation at the file
> system level.
> --|||Space isn't allocated automatically in advance. Imagine someone want to add an order, for instance.
An insert is performed by the application. The file is full so the file need to be expanded, while
the user waits.
And imagine if the grow takes long time, so the application does a time-out during this. The grow
will be rolled back and we are back to square zero. For medium to large databases, you want to
pre-allocate storage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...
> Thank you for your reply.
> I am just wondering, why in my case even though "Unrestricted file growth" and "Automatically
> grow file" are checked, it did not allocate the space automatically ?
> Is this the case like mentioned in your article
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are situations where autogrow
> doesn't "catch up" with the space usage requirements" ?
> So, the better way is to always manually allocate the space ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
>> Yes, but the users who need space during the grow need to wait for the grow to finish. Also, a
>> lot of grow will cause fragmentation at the file system level.
>> --
>|||I see. In my case the database is about 2 gig, so when someone want to add a
new record, the grow takes a long time, and it times out, so it did not
allocate the space.
The application did get the "Timeout expired" error.
I will pre-allocate the space from now on.
Thanks a lot for your help. I appreciate it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTIhk9AfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Space isn't allocated automatically in advance. Imagine someone want to
> add an order, for instance. An insert is performed by the application. The
> file is full so the file need to be expanded, while the user waits.
> And imagine if the grow takes long time, so the application does a
> time-out during this. The grow will be rolled back and we are back to
> square zero. For medium to large databases, you want to pre-allocate
> storage.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...
>> Thank you for your reply.
>> I am just wondering, why in my case even though "Unrestricted file
>> growth" and "Automatically grow file" are checked, it did not allocate
>> the space automatically ?
>> Is this the case like mentioned in your article
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are
>> situations where autogrow doesn't "catch up" with the space usage
>> requirements" ?
>> So, the better way is to always manually allocate the space ?
>> Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
>> Yes, but the users who need space during the grow need to wait for the
>> grow to finish. Also, a lot of grow will cause fragmentation at the file
>> system level.
>> --
>>
>|||One more thing.
I see there is a wizard to create an alert. If I want to create an alert
(right click on the database, View - Taskpad, tab "Wizards" - Create an
Alert) when it is time for me to pre-allocate space again, what "error
severity" shall I select from the wizard drop down box ?
Thanks.|||Alerts are either based on perfmon counters. There's no perfmon counter for free space in a database
data file, but there is for a database tlog file. Or they are based on messages to eventlog. But
messages from SQL server are not written to eventlog when a database ix X percent full.
You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db" procedure. I suggest you
read the code and adapt it to your needs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
> One more thing.
> I see there is a wizard to create an alert. If I want to create an alert (right click on the
> database, View - Taskpad, tab "Wizards" - Create an Alert) when it is time for me to pre-allocate
> space again, what "error severity" shall I select from the wizard drop down box ?
> Thanks.
>|||Thanks a lot for your help.
When you said "perfmon counters" did you mean performance counters ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ux5vfwHfFHA.1044@.tk2msftngp13.phx.gbl...
> Alerts are either based on perfmon counters. There's no perfmon counter
> for free space in a database data file, but there is for a database tlog
> file. Or they are based on messages to eventlog. But messages from SQL
> server are not written to eventlog when a database ix X percent full.
> You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db"
> procedure. I suggest you read the code and adapt it to your needs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
>> One more thing.
>> I see there is a wizard to create an alert. If I want to create an alert
>> (right click on the database, View - Taskpad, tab "Wizards" - Create an
>> Alert) when it is time for me to pre-allocate space again, what "error
>> severity" shall I select from the wizard drop down box ?
>> Thanks.
>>
>|||Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:%234l5YnLfFHA.3584@.TK2MSFTNGP09.phx.gbl...
> Thanks a lot for your help.
> When you said "perfmon counters" did you mean performance counters ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ux5vfwHfFHA.1044@.tk2msftngp13.phx.gbl...
>> Alerts are either based on perfmon counters. There's no perfmon counter for free space in a
>> database data file, but there is for a database tlog file. Or they are based on messages to
>> eventlog. But messages from SQL server are not written to eventlog when a database ix X percent
>> full.
>> You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db" procedure. I suggest you
>> read the code and adapt it to your needs.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "fniles" <fniles@.pfmail.com> wrote in message news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
>> One more thing.
>> I see there is a wizard to create an alert. If I want to create an alert (right click on the
>> database, View - Taskpad, tab "Wizards" - Create an Alert) when it is time for me to
>> pre-allocate space again, what "error severity" shall I select from the wizard drop down box ?
>> Thanks.
>>
>>
>
"Unrestricted file growth". Why in the TaskPad view, the free "space
allocated" is 0 ? Thank you.The file has a certain size. For that size, it seems you have 0 free. The file can grow as space is
needed. Note that grow hurts performance (growing 100MB can take some 10 seconds during the user who
is victim for the grow sits and wait for the operation to be performed). I suggest you pre-allocate
storage so you have some free space, and of course not shrink the files regularly
(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/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
> We set our SQL Server database to "Automatically grow file" and "Unrestricted file growth". Why in
> the TaskPad view, the free "space allocated" is 0 ? Thank you.
>|||Thank you for your reply.
By checking the "Unrestricted file growth" and "Automatically grow file" ,
doesn't it suppose to allocate the space automatically (instead of having to
pre-allocate the storage manually) ?
Thank you.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
> The file has a certain size. For that size, it seems you have 0 free. The
> file can grow as space is needed. Note that grow hurts performance
> (growing 100MB can take some 10 seconds during the user who is victim for
> the grow sits and wait for the operation to be performed). I suggest you
> pre-allocate storage so you have some free space, and of course not shrink
> the files regularly
> (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/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> We set our SQL Server database to "Automatically grow file" and
>> "Unrestricted file growth". Why in the TaskPad view, the free "space
>> allocated" is 0 ? Thank you.
>|||Yes, but the users who need space during the grow need to wait for the grow to finish. Also, a lot
of grow will cause fragmentation at the file system level.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:%237ldhWAfFHA.1036@.tk2msftngp13.phx.gbl...
> Thank you for your reply.
> By checking the "Unrestricted file growth" and "Automatically grow file" , doesn't it suppose to
> allocate the space automatically (instead of having to pre-allocate the storage manually) ?
> Thank you.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
>> The file has a certain size. For that size, it seems you have 0 free. The file can grow as space
>> is needed. Note that grow hurts performance (growing 100MB can take some 10 seconds during the
>> user who is victim for the grow sits and wait for the operation to be performed). I suggest you
>> pre-allocate storage so you have some free space, and of course not shrink the files regularly
>> (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/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "fniles" <fniles@.pfmail.com> wrote in message news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
>> We set our SQL Server database to "Automatically grow file" and "Unrestricted file growth". Why
>> in the TaskPad view, the free "space allocated" is 0 ? Thank you.
>>
>|||Thank you for your reply.
I am just wondering, why in my case even though "Unrestricted file growth"
and "Automatically grow file" are checked, it did not allocate the space
automatically ?
Is this the case like mentioned in your article
http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are
situations where autogrow doesn't "catch up" with the space usage
requirements" ?
So, the better way is to always manually allocate the space ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
> Yes, but the users who need space during the grow need to wait for the
> grow to finish. Also, a lot of grow will cause fragmentation at the file
> system level.
> --|||Space isn't allocated automatically in advance. Imagine someone want to add an order, for instance.
An insert is performed by the application. The file is full so the file need to be expanded, while
the user waits.
And imagine if the grow takes long time, so the application does a time-out during this. The grow
will be rolled back and we are back to square zero. For medium to large databases, you want to
pre-allocate storage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...
> Thank you for your reply.
> I am just wondering, why in my case even though "Unrestricted file growth" and "Automatically
> grow file" are checked, it did not allocate the space automatically ?
> Is this the case like mentioned in your article
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are situations where autogrow
> doesn't "catch up" with the space usage requirements" ?
> So, the better way is to always manually allocate the space ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
>> Yes, but the users who need space during the grow need to wait for the grow to finish. Also, a
>> lot of grow will cause fragmentation at the file system level.
>> --
>|||I see. In my case the database is about 2 gig, so when someone want to add a
new record, the grow takes a long time, and it times out, so it did not
allocate the space.
The application did get the "Timeout expired" error.
I will pre-allocate the space from now on.
Thanks a lot for your help. I appreciate it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTIhk9AfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Space isn't allocated automatically in advance. Imagine someone want to
> add an order, for instance. An insert is performed by the application. The
> file is full so the file need to be expanded, while the user waits.
> And imagine if the grow takes long time, so the application does a
> time-out during this. The grow will be rolled back and we are back to
> square zero. For medium to large databases, you want to pre-allocate
> storage.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...
>> Thank you for your reply.
>> I am just wondering, why in my case even though "Unrestricted file
>> growth" and "Automatically grow file" are checked, it did not allocate
>> the space automatically ?
>> Is this the case like mentioned in your article
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are
>> situations where autogrow doesn't "catch up" with the space usage
>> requirements" ?
>> So, the better way is to always manually allocate the space ?
>> Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
>> Yes, but the users who need space during the grow need to wait for the
>> grow to finish. Also, a lot of grow will cause fragmentation at the file
>> system level.
>> --
>>
>|||One more thing.
I see there is a wizard to create an alert. If I want to create an alert
(right click on the database, View - Taskpad, tab "Wizards" - Create an
Alert) when it is time for me to pre-allocate space again, what "error
severity" shall I select from the wizard drop down box ?
Thanks.|||Alerts are either based on perfmon counters. There's no perfmon counter for free space in a database
data file, but there is for a database tlog file. Or they are based on messages to eventlog. But
messages from SQL server are not written to eventlog when a database ix X percent full.
You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db" procedure. I suggest you
read the code and adapt it to your needs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
> One more thing.
> I see there is a wizard to create an alert. If I want to create an alert (right click on the
> database, View - Taskpad, tab "Wizards" - Create an Alert) when it is time for me to pre-allocate
> space again, what "error severity" shall I select from the wizard drop down box ?
> Thanks.
>|||Thanks a lot for your help.
When you said "perfmon counters" did you mean performance counters ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ux5vfwHfFHA.1044@.tk2msftngp13.phx.gbl...
> Alerts are either based on perfmon counters. There's no perfmon counter
> for free space in a database data file, but there is for a database tlog
> file. Or they are based on messages to eventlog. But messages from SQL
> server are not written to eventlog when a database ix X percent full.
> You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db"
> procedure. I suggest you read the code and adapt it to your needs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
>> One more thing.
>> I see there is a wizard to create an alert. If I want to create an alert
>> (right click on the database, View - Taskpad, tab "Wizards" - Create an
>> Alert) when it is time for me to pre-allocate space again, what "error
>> severity" shall I select from the wizard drop down box ?
>> Thanks.
>>
>|||Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:%234l5YnLfFHA.3584@.TK2MSFTNGP09.phx.gbl...
> Thanks a lot for your help.
> When you said "perfmon counters" did you mean performance counters ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ux5vfwHfFHA.1044@.tk2msftngp13.phx.gbl...
>> Alerts are either based on perfmon counters. There's no perfmon counter for free space in a
>> database data file, but there is for a database tlog file. Or they are based on messages to
>> eventlog. But messages from SQL server are not written to eventlog when a database ix X percent
>> full.
>> You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db" procedure. I suggest you
>> read the code and adapt it to your needs.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "fniles" <fniles@.pfmail.com> wrote in message news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
>> One more thing.
>> I see there is a wizard to create an alert. If I want to create an alert (right click on the
>> database, View - Taskpad, tab "Wizards" - Create an Alert) when it is time for me to
>> pre-allocate space again, what "error severity" shall I select from the wizard drop down box ?
>> Thanks.
>>
>>
>
Subscribe to:
Posts (Atom)