Microsoft SQL Server forum - SQL Automatic Growth

Author

SQL Automatic Growth

jlgreg36

2005-05-27, 7:23 am

We use SQL 2000 and our database is configured to grow automatically by
10%. Currently 96% of our database is used. At what point will the
database expand - what is the trigger point?

Greg D. Moore \(Strider\)

2005-05-27, 9:23 am

"jlgreg36" <jlgreg@hotmail.com> wrote in message
news:1117190814.354304.267170@z14g2000cwz.googlegroups.com...
> We use SQL 2000 and our database is configured to grow automatically by
> 10%. Currently 96% of our database is used. At what point will the
> database expand - what is the trigger point?

When it's full.

Which can be a problem.

Let's say your DB is 10 GB in size and it's full and you attempt to do an
insert.

The insert will be blocked until SQL Server allocates 1 GB of disk space
which can take some time.

(Now imagine a 100GB DB that tries to allocate 10 GB of disk space. :)

Generally you're better off monitoring it yourself and allocating space as
required. Or at the very least allocate fixed amounts of diskspace so you
don't get into a geometrically increasing amount of time for each
allocation.

>

Erland Sommarskog

2005-05-27, 8:23 pm

Greg D. Moore (Strider) (mooregr_deleteth1s@
greenms.com) writes:
> "jlgreg36" <jlgreg@hotmail.com> wrote in message
> news:1117190814.354304.267170@z14g2000cwz.googlegroups.com...
>
> When it's full.

More precisely, where there are no more free extents, and SQL Server
needs a new extent. At this point, there is still some free space
available in the database, although this is only of academic interest.

> Generally you're better off monitoring it yourself and allocating space as
> required. Or at the very least allocate fixed amounts of diskspace so you
> don't get into a geometrically increasing amount of time for each
> allocation.

Completely agree!

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links