Auto-grow option isnt working

We're using SQL2000 on Windows 2000 Server, but this is a problem
we've had on one particular database since SQL7 on NT4.

The database in question is set to autogrow by 10% (currently sitting
at 31Gb total size). However, last week users complained of a
slowdown in performance. When we checked we found that only 14Mb was
free on the database (we thought it would've grown automatically
before then), and when we added an additional 1Gb manually performance
picked up.

Does SQLServer wait until all the space is used up (i.e. 0% free)
before autogrowing? Even at that, we've never actually had the
database grow automatically - we've always had to add space manually.
Settings on this database, and one that does grow automatically,
appear to be the same (have also checked via sp_helpdb). So where
does the problem lie?

Any help you can give would be greatly appreciated.Do you have large transactions occurring in that database? If so, this KB
article might apply to your case:

http://support.microsoft.com/defaul...kb;en-us;305635

Simon

"LizP" <liz.porteous@.scottishfriendly.co.uk> wrote in message
news:8170ed5a.0306240753.1dd021f8@.posting.google.c om...
> We're using SQL2000 on Windows 2000 Server, but this is a problem
> we've had on one particular database since SQL7 on NT4.
> The database in question is set to autogrow by 10% (currently sitting
> at 31Gb total size). However, last week users complained of a
> slowdown in performance. When we checked we found that only 14Mb was
> free on the database (we thought it would've grown automatically
> before then), and when we added an additional 1Gb manually performance
> picked up.
> Does SQLServer wait until all the space is used up (i.e. 0% free)
> before autogrowing? Even at that, we've never actually had the
> database grow automatically - we've always had to add space manually.
> Settings on this database, and one that does grow automatically,
> appear to be the same (have also checked via sp_helpdb). So where
> does the problem lie?
> Any help you can give would be greatly appreciated.

auto-grow gotcha

I made a database to hold recordings of calls made to our customers.
When I made it I set the size of the primary datafile to 18GB. It's
been running flawlessly for over 10 months. A few days ago the users
were suddenly no longer able to save the recordings to the database.
They got an error message to the effect that the timeout had expired.
The failure occurred on the .Execute statement of the Command that
calls the stored procedure.

I noticed that the data had reached the size allocated for the file.
The file was set to auto-grow (5%). However, since I couldn't find
anything else wrong, and since the test version of the database (which
only has 15GB of data in an 18GB-dimensioned file) did not exhibit the
same behavior, I decided to try increasing the size of the file with
an ALTER DATABASE statement. I increased it to 21GB. Lo and behold,
the problem disappeared.

Here's what I think might be going on: The default timeout for the
ADO Command object is 30 seconds... this is probably not long enough
for SQL Server to add 900 MB to the datafile, therefore the Command
timeout expired. So from now on instead of relying on auto-grow, I'm
going to just make sure the datafile always has plenty of headroom.

FWIW."Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
news:0rlntvou1j40dr2fbo1fs5uv06ir3cf89a@.4ax.com...
> I made a database to hold recordings of calls made to our customers.
> When I made it I set the size of the primary datafile to 18GB. It's
> been running flawlessly for over 10 months. A few days ago the users
> were suddenly no longer able to save the recordings to the database.
> They got an error message to the effect that the timeout had expired.
> The failure occurred on the .Execute statement of the Command that
> calls the stored procedure.
> I noticed that the data had reached the size allocated for the file.
> The file was set to auto-grow (5%). However, since I couldn't find
> anything else wrong, and since the test version of the database (which
> only has 15GB of data in an 18GB-dimensioned file) did not exhibit the
> same behavior, I decided to try increasing the size of the file with
> an ALTER DATABASE statement. I increased it to 21GB. Lo and behold,
> the problem disappeared.
> Here's what I think might be going on: The default timeout for the
> ADO Command object is 30 seconds... this is probably not long enough
> for SQL Server to add 900 MB to the datafile, therefore the Command
> timeout expired. So from now on instead of relying on auto-grow, I'm
> going to just make sure the datafile always has plenty of headroom.

The other option is to set it to grow by a fixed amount (say 500 MB) each
time rather than a %. As you found out, that % growth adds up quickly.

But I think your solution is the best, to pro-actively grow it.

(Since the next problem you'll encounter is is needing to grow say 900MB,
but finding out you have 500 MB free. Autogrow won't work and you're
basically stuck. :-)

And yes, I've been bit by this too.

> FWIW.

Auto-Grow and Shrink Via Code

Hey guys,
Does anybody know of a way to turn on the Auto-Grow feature for a
transaction log via T-SQL code? What we want to do is during a
scheduled job, turn the auto grow feature on then after the job is
finished, shrink the log, then cut auto grow back off?
Any thoughts? Thanks in advance!
Hunter
**************Please Post to Group so that all can benefit!Have you looked at the ALTER DATABASE topic in Books Online? Of particular
interest:
AUTO_SHRINK ON | OFF
If ON is specified, the database files are candidates for automatic periodic
shrinking.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Hunter" <bamared36054@.yahoo.com> wrote in message
news:2a0a8bc1.0312020836.3923452e@.posting.google.com...
> Hey guys,
> Does anybody know of a way to turn on the Auto-Grow feature for a
> transaction log via T-SQL code? What we want to do is during a
> scheduled job, turn the auto grow feature on then after the job is
> finished, shrink the log, then cut auto grow back off?
> Any thoughts? Thanks in advance!
> Hunter
> **************Please Post to Group so that all can benefit!|||Sorry, wrong section... 'grow' != 'shrink' Aaron! Look under filespec:
< filespec > ::=( NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] )
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/