auto-grow gotcha

[Ellen K.]

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.

[Greg D. Moore \(Strider\)]

"Ellen K." <72************************@compuserve.com> wrote in message
news:0r********************************@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.