Automatic FileGrowth

At what point does SQL actually begin the file growth?
Is it when the database reaches a secific percentage of
the file size or when the database reaches a specific
size. For example, if I have a 100GB database (90 GB
used, 10GB free) and it is set to grow 10%, does the
database grow 10% when the database reaches 91GB or when
it reaches 100GB?It doesn't grow until it is 100% full and more space is needed due to some
SQL statement is executed (an INSERT, for instance).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Philip Wilhelm" <philip.wilhelm@.astongroup-us.com> wrote in message
news:05ca01c3a92e$c81d2990$a501280a@.phx.gbl...
> At what point does SQL actually begin the file growth?
> Is it when the database reaches a secific percentage of
> the file size or when the database reaches a specific
> size. For example, if I have a 100GB database (90 GB
> used, 10GB free) and it is set to grow 10%, does the
> database grow 10% when the database reaches 91GB or when
> it reaches 100GB?|||Philip
http://www.sql-server-performance.com/database_settings.asp
"Philip Wilhelm" <philip.wilhelm@.astongroup-us.com> wrote in message
news:05ca01c3a92e$c81d2990$a501280a@.phx.gbl...
> At what point does SQL actually begin the file growth?
> Is it when the database reaches a secific percentage of
> the file size or when the database reaches a specific
> size. For example, if I have a 100GB database (90 GB
> used, 10GB free) and it is set to grow 10%, does the
> database grow 10% when the database reaches 91GB or when
> it reaches 100GB?

Autogrowth for MDF and LDF files

I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?

Thanks,

CarlosI have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?

Thanks,

Carlos

I generally allow dbs that are smaller that 20 GB to autogrow by the default setting (10%). But once they get above that size, I manage them manually and ensure that there is enough empty space in the datafile to get through until the next maintenance window.

Growing a data file in SQL 2005 is not as expensive (IO wise) as it was in SQL 2000, but I still think you want to keep a closer eye on things once they get above 20 GB. 20 GB is admittedly arbitrary. If you have space issues, you might consider a lower threshold.

Regards,

hmscott|||It depends on what your database is used for. Mine are configured to grow by several hundred MB\ a few GB but that is because there are small numbers of massive modifications. An OLTP database should not, IMHO, be growing that much each time. The user that submitted the modification that triggers a 500MB growth could be twiddling their thumbs for quite some time cursing the system as they do.

I too would manage the growth at peak periods with a view to eliminating\ reducing autogrowth as much as possible.|||Your file growth is because of your 500MB setting. When SQL starts to run out of space it will adjust by 500MB. If has to adjust twice a day, there's your 1GB. I think you're fine where you're at. Just make sure you have enough drive space. I'm sure your growth will plateau.

Auto growth and offline database

Does a heavy used database go through being locked up as it autogrows ?Any users that update or insert new rows after the user who initiated an
autogrow will wait until the growth is finished. If they are only selecting
there is no issue.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.hotmail.com> wrote in message
news:O2Frv7rFIHA.1316@.TK2MSFTNGP02.phx.gbl...
> Does a heavy used database go through being locked up as it autogrows ?
>|||If it is the trasaction log that is growing and and the users are doing
anything that generates a log record, it can look like the system is
hanging.
"Hassan" <hassan@.hotmail.com> wrote in message
news:O2Frv7rFIHA.1316@.TK2MSFTNGP02.phx.gbl...
> Does a heavy used database go through being locked up as it autogrows ?
>|||Hassan;
This KB article has good info on the autogrow behavior and its potential
impact on concurrency.
Linchi
"Hassan" wrote:
> Does a heavy used database go through being locked up as it autogrows ?
>
>|||Linchi,
Which KB article would that be? :)
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:2E2D0152-266B-459A-A542-6C37A24A8AE3@.microsoft.com...
> Hassan;
> This KB article has good info on the autogrow behavior and its potential
> impact on concurrency.
> Linchi
> "Hassan" wrote:
>> Does a heavy used database go through being locked up as it autogrows ?
>>|||Oops! http://support.microsoft.com/kb/315512/
Linchi
"Andrew J. Kelly" wrote:
> Linchi,
> Which KB article would that be? :)
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:2E2D0152-266B-459A-A542-6C37A24A8AE3@.microsoft.com...
> > Hassan;
> >
> > This KB article has good info on the autogrow behavior and its potential
> > impact on concurrency.
> >
> > Linchi
> >
> > "Hassan" wrote:
> >
> >> Does a heavy used database go through being locked up as it autogrows ?
> >>
> >>
> >>
>

auto grow

How expensive is auto grow when it it kicks in? We're planing on monitoring database growth and resizing as needed, but want to set this feature as well. Is it better to set this high (ie 10% on a 5gb database) or low (ie fixed 50 mb)?
thanks
The larger the database the more expensive it becomes. A small DB growing a
little is of course a small expense. It is better to plan ahead for a
larger DB and try to grow it in advance when it will not effect your users.
Still keep auto grow enabled in case you miss a step but try and keep ahead
of it so it does not happen and tick off your user base.
Jeff Duncan
MCDBA, MCSE+I
"Peter Feakins" <anonymous@.discussions.microsoft.com> wrote in message
news:4F1E0506-78D8-4C7C-B3C2-953C5CCE8874@.microsoft.com...
> How expensive is auto grow when it it kicks in? We're planing on
monitoring database growth and resizing as needed, but want to set this
feature as well. Is it better to set this high (ie 10% on a 5gb database)
or low (ie fixed 50 mb)?
> thanks
|||You can find these issues addressed and suggestions on using
autogrow in the following article:
INF: Considerations for Autogrow and Autoshrink
Configuration
http://www.support.microsoft.com/?id=315512
-Sue
On Mon, 3 May 2004 07:56:11 -0700, "Peter Feakins"
<anonymous@.discussions.microsoft.com> wrote:

>How expensive is auto grow when it it kicks in? We're planing on monitoring database growth and resizing as needed, but want to set this feature as well. Is it better to set this high (ie 10% on a 5gb database) or low (ie fixed 50 mb)?
>thanks

auto grow

How expensive is auto grow when it it kicks in? We're planing on monitoring
database growth and resizing as needed, but want to set this feature as well
. Is it better to set this high (ie 10% on a 5gb database) or low (ie fixed
50 mb)?
thanksThe larger the database the more expensive it becomes. A small DB growing a
little is of course a small expense. It is better to plan ahead for a
larger DB and try to grow it in advance when it will not effect your users.
Still keep auto grow enabled in case you miss a step but try and keep ahead
of it so it does not happen and tick off your user base.
Jeff Duncan
MCDBA, MCSE+I
"Peter Feakins" <anonymous@.discussions.microsoft.com> wrote in message
news:4F1E0506-78D8-4C7C-B3C2-953C5CCE8874@.microsoft.com...
> How expensive is auto grow when it it kicks in? We're planing on
monitoring database growth and resizing as needed, but want to set this
feature as well. Is it better to set this high (ie 10% on a 5gb database)
or low (ie fixed 50 mb)?
> thanks|||You can find these issues addressed and suggestions on using
autogrow in the following article:
INF: Considerations for Autogrow and Autoshrink
Configuration
http://www.support.microsoft.com/?id=315512
-Sue
On Mon, 3 May 2004 07:56:11 -0700, "Peter Feakins"
<anonymous@.discussions.microsoft.com> wrote:

>How expensive is auto grow when it it kicks in? We're planing on monitoring
database growth and resizing as needed, but want to set this feature as wel
l. Is it better to set this high (ie 10% on a 5gb database) or low (ie fixe
d 50 mb)?
>thanks