automatically grow file

SQL Server 7.00, Service Pack 6
I had an insert statement that stopped working the other day. I figured out
that it had to do with the fact that "space available" on the database was
very small. I had "automatically grow file" on the db set to 10%, and I had
plenty of disk space. I think that when my client called the insert
statement, the db was trying to automatically grow, but it was taking so
long that the client timed out.
I finally called:
ALTER DATABASE xxx
MODIFY FILE
(NAME = 'xxx_data',
SIZE = 3000MB)
which took a minute and a half, and then the insert statement began working
quickly again.
I have a couple of half-formed ideas on how to deal with this over the long
term.
My db is now 3GB. If I set "automatically grow file" to 1%, instead of 10%,
does that mean that the automatic growing next time will be quicker? Is
the time it takes proportional to the amount of space we are adding?
Maybe I could create a weekly job that looks to see if the space available
on a database is less than 50MB or so, and if so, expands the database size.
I'm not sure how to write this. I can call sp_spaceused, but I don't know
how to stick "unallocated space" into a variable, since sp_spaceused returns
two datasets.
Any other ideas?
Thanks,
GeorgeThere is probably a simpler way to do this, but...
I would follow your weekly idea.
If you run sp_helptext sp_spaceused you will get back the queries used to
run the sp_spaceused command.
I would probably take that code and create a new sproc. In the new sproc,
instead of returning the results, you can check them there and make the
appropriate changes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Sp_helptext is useful. Thanks.
I'm mostly there, but now I am having trouble passing a parameter into the
alter database command.
declare @.newsize varchar(8)
...
ALTER DATABASE xyz MODIFY FILE
(NAME = xyz_data, SIZE = @.newsize)
Line 5: Incorrect syntax near '@.newsize'.
Thanks,
George|||Seems you can't use a variable for the size, quite simply. Try using dynamic SQL to EXEC the
statement instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Wynne" <george@.nssco.com> wrote in message news:O5hyQ6FkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Sp_helptext is useful. Thanks.
> I'm mostly there, but now I am having trouble passing a parameter into the
> alter database command.
> declare @.newsize varchar(8)
> ...
> ALTER DATABASE xyz MODIFY FILE
> (NAME = xyz_data, SIZE = @.newsize)
> Line 5: Incorrect syntax near '@.newsize'.
> Thanks,
> George
>sql