DB autogrow question

Dear all,
when I have the autogrow option set on a database - when
the database is being expanded, are processes blocked
against that database. If so, what type of processes
(inserts, updates, backups etc ) >
I'm trying to work out if it is better to create tempdb
small - and let it grow often. Or size it initially larger
with only an occasional expansion.
Thanks in advance.Steve
To a degree you have answered your own question.
You should size a database to the size it needs to be,
tempdb or any other database.
On the other hand of course if you have say a tempdb
database that is say 500mb and this is fine for all
processes except one job that runs once a month and needs
a 2gb tampdb. You should be looking at that job to see if
it is being run the best way it can be.
General advice on database growth, if your database keeps
on growing don't keep adding little bits that last a week
or two then you need to grow some more, do enough for 6 -
12 months at a time.
Hope this helps
John|||Thanks John, but do you know the actual level of database
blocking/overhead caused by a database expansion ?
>--Original Message--
>Steve
>To a degree you have answered your own question.
>You should size a database to the size it needs to be,
>tempdb or any other database.
>On the other hand of course if you have say a tempdb
>database that is say 500mb and this is fine for all
>processes except one job that runs once a month and needs
>a 2gb tampdb. You should be looking at that job to see if
>it is being run the best way it can be.
>General advice on database growth, if your database keeps
>on growing don't keep adding little bits that last a week
>or two then you need to grow some more, do enough for 6 -
>12 months at a time.
>Hope this helps
>John
>.
>|||Steve
I don't think it should cause blocking, as it is only
formating new database space. That process will use system
resources though, so impact probably depends on how much
spare capacity (CPU, Memory and I/O) you have on your
system.
See this article on pefmon on tips on how to check your
current usage.
http://www.sql-server-
performance.com/performance_monitor_tips.asp
Regards
John|||It's not blocking in the true sql server sense but since the work can not be
done until the file has finished expanding, everything is temporarily put on
hold (kind of). You will definitively see slowdowns when the growth occurs
if there is current activity in that file. It wouldn't need to grow if
there wasn't<g>. And as John points out it also takes many other resources
to make it happen. If you know the file is not large enough to do the job,
even if it's for 1 minute a day you should size it accordingly and do not
shrink it when done. There is no penalty for too large a file but there is
one for it being too small.
--
Andrew J. Kelly
SQL Server MVP
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:494c01c35687$76dacb90$a001280a@.phx.gbl...
> Steve
> I don't think it should cause blocking, as it is only
> formating new database space. That process will use system
> resources though, so impact probably depends on how much
> spare capacity (CPU, Memory and I/O) you have on your
> system.
> See this article on pefmon on tips on how to check your
> current usage.
> http://www.sql-server-
> performance.com/performance_monitor_tips.asp
> Regards
> John