Automatically grow database

If the autogrow is set on a database primary file group
when does the server actually grow the file? Does it wait
for an out of space condition or is it automated?It is before an out of space condtion. But you can set up a job to automate
it with the ALTER DATABASE statement.
"Kirk" <anonymous@.discussions.microsoft.com> wrote in message
news:5ed001c3df71$7aa9c230$7d02280a@.phx.gbl...

quote:

---

> If the autogrow is set on a database primary file group
> when does the server actually grow the file? Does it wait
> for an out of space condition or is it automated?

---

|||The reason I ask is that we have the primary file group to
autogrow at 100mb. We have a maintenance plan that runs
every Sunday. For the past 2 Sunday's the job has failed
due to out of space. There is plenty of space on the
drive for the file to grow. Not sure why the job is
failing on space issue. That is why I ask if it is on
error does it grow.|||Having autogrow is better than nothing. But better yet, don't leave it
solely for SQL Server. The better way is to size your db, forecast its
growth, and allocate space accordingly. Leave the autogrow on but keep
checking back whether there is need of growing again, and if needed, do it
manually at a not-so-busy time. Autogrow can take time so long that your
application may error out while waiting for the growth (though it's not
likely in your case of autogrow size).
Not sure what your problem is. You have a plan to grow the db file every
sunday? That doesn't sound right. What for job was failing? What's the
role of the maintenance plan in your problem?
<anonymous@.discussions.microsoft.com> wrote in message
news:126301c3df8b$67613d60$a001280a@.phx.gbl...

quote:

---

> The reason I ask is that we have the primary file group to
> autogrow at 100mb. We have a maintenance plan that runs
> every Sunday. For the past 2 Sunday's the job has failed
> due to out of space. There is plenty of space on the
> drive for the file to grow. Not sure why the job is
> failing on space issue. That is why I ask if it is on
> error does it grow.

---

Automatically grow database

If the autogrow is set on a database primary file group
when does the server actually grow the file? Does it wait
for an out of space condition or is it automated?Yes, on reaching its current size it will grow by either a
percentage of the current size or as a fixed number of MB.
It will take the space on the Hard Disk up, so make sure
you have plenty of disk space.
J
>--Original Message--
>If the autogrow is set on a database primary file group
>when does the server actually grow the file? Does it
wait
>for an out of space condition or is it automated?
>.
>|||It is before an out of space condtion. But you can set up a job to automate
it with the ALTER DATABASE statement.
"Kirk" <anonymous@.discussions.microsoft.com> wrote in message
news:5ed001c3df71$7aa9c230$7d02280a@.phx.gbl...
> If the autogrow is set on a database primary file group
> when does the server actually grow the file? Does it wait
> for an out of space condition or is it automated?|||The reason I ask is that we have the primary file group to
autogrow at 100mb. We have a maintenance plan that runs
every Sunday. For the past 2 Sunday's the job has failed
due to out of space. There is plenty of space on the
drive for the file to grow. Not sure why the job is
failing on space issue. That is why I ask if it is on
error does it grow.|||Having autogrow is better than nothing. But better yet, don't leave it
solely for SQL Server. The better way is to size your db, forecast its
growth, and allocate space accordingly. Leave the autogrow on but keep
checking back whether there is need of growing again, and if needed, do it
manually at a not-so-busy time. Autogrow can take time so long that your
application may error out while waiting for the growth (though it's not
likely in your case of autogrow size).
Not sure what your problem is. You have a plan to grow the db file every
sunday? That doesn't sound right. What for job was failing? What's the
role of the maintenance plan in your problem?
<anonymous@.discussions.microsoft.com> wrote in message
news:126301c3df8b$67613d60$a001280a@.phx.gbl...
> The reason I ask is that we have the primary file group to
> autogrow at 100mb. We have a maintenance plan that runs
> every Sunday. For the past 2 Sunday's the job has failed
> due to out of space. There is plenty of space on the
> drive for the file to grow. Not sure why the job is
> failing on space issue. That is why I ask if it is on
> error does it grow.

Autogrow Timeouts

Hello - Can anyone direct me to good articles on Alter database and Autogrow?
We have a shared server where users in one database got timeouts. Looking
back at the logs, the database was trying to grow during that time frame, but
it timed out. Please see part of log:
2006-10-17 15:00:32.97 spid622 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 15922 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:00:44.41 spid168 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 11390 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:00:46.14 spid460 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 1672 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:05.19 spid745 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 18968 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:05.62 spid478 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 406 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:10.03 spid213 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 4390 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:09.95 spid175 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 59906 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:23.48 spid478 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 13515 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:47.55 spid69 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 24047 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:49.81 spid875 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 2265 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:03:12.64 spid168 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 22829 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
The database is about 2.7 gb, and the autogrow is set for 10%. So it was
trying to add maybe 300 mb, but it kept timing out. I could understand it
being an issue for a larger number, but 300 mb is not very much.
When the alter database add filespace happens, is the entire database
inaccessible during that time? Or is it just the space it is adding that is
inaccessible?
I am wondering if the autogrow timeouts and the user timeouts had the same
thing happening to make them timeout. Or if the autogrow attempts were
causing the user timeouts. Seems that cpu on the server were normal, and no
other databases had complaints of slows. The autogrow attempts kept
happening for about an hour and a half before it was succesful. Please help!
Below are two KB about the issue:
http://support.microsoft.com/kb/315512/
INF: Considerations for Autogrow and Autoshrink configuration in SQL
Server
http://support.microsoft.com/kb/305635/
PRB: A Timeout Occurs When a Database Is Automatically Expanding
Mitch wrote:
> Hello - Can anyone direct me to good articles on Alter database and Autogrow?
> We have a shared server where users in one database got timeouts. Looking
> back at the logs, the database was trying to grow during that time frame, but
> it timed out. Please see part of log:
> 2006-10-17 15:00:32.97 spid622 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 15922 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:00:44.41 spid168 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 11390 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:00:46.14 spid460 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 1672 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:05.19 spid745 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 18968 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:05.62 spid478 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 406 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:10.03 spid213 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 4390 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:09.95 spid175 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 59906 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:23.48 spid478 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 13515 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:47.55 spid69 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 24047 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:49.81 spid875 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 2265 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:03:12.64 spid168 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 22829 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> The database is about 2.7 gb, and the autogrow is set for 10%. So it was
> trying to add maybe 300 mb, but it kept timing out. I could understand it
> being an issue for a larger number, but 300 mb is not very much.
> When the alter database add filespace happens, is the entire database
> inaccessible during that time? Or is it just the space it is adding that is
> inaccessible?
> I am wondering if the autogrow timeouts and the user timeouts had the same
> thing happening to make them timeout. Or if the autogrow attempts were
> causing the user timeouts. Seems that cpu on the server were normal, and no
> other databases had complaints of slows. The autogrow attempts kept
> happening for about an hour and a half before it was succesful. Please help!

Autogrow Timeouts

Hello - Can anyone direct me to good articles on Alter database and Autogrow
?
We have a shared server where users in one database got timeouts. Looking
back at the logs, the database was trying to grow during that time frame, bu
t
it timed out. Please see part of log:
2006-10-17 15:00:32.97 spid622 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 15922 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:00:44.41 spid168 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 11390 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:00:46.14 spid460 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 1672 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:05.19 spid745 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 18968 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:05.62 spid478 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 406 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:10.03 spid213 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 4390 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:09.95 spid175 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 59906 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:23.48 spid478 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 13515 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:47.55 spid69 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 24047 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:49.81 spid875 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 2265 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:03:12.64 spid168 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 22829 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
The database is about 2.7 gb, and the autogrow is set for 10%. So it was
trying to add maybe 300 mb, but it kept timing out. I could understand it
being an issue for a larger number, but 300 mb is not very much.
When the alter database add filespace happens, is the entire database
inaccessible during that time? Or is it just the space it is adding that is
inaccessible?
I am wondering if the autogrow timeouts and the user timeouts had the same
thing happening to make them timeout. Or if the autogrow attempts were
causing the user timeouts. Seems that cpu on the server were normal, and no
other databases had complaints of slows. The autogrow attempts kept
happening for about an hour and a half before it was succesful. Please help
!Below are two KB about the issue:
http://support.microsoft.com/kb/315512/
INF: Considerations for Autogrow and Autoshrink configuration in SQL
Server
http://support.microsoft.com/kb/305635/
PRB: A Timeout Occurs When a Database Is Automatically Expanding
Mitch wrote:[vbcol=seagreen]
> Hello - Can anyone direct me to good articles on Alter database and Autogr
ow?
> We have a shared server where users in one database got timeouts. Lookin
g
> back at the logs, the database was trying to grow during that time frame,
but
> it timed out. Please see part of log:
> 2006-10-17 15:00:32.97 spid622 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 15922 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:00:44.41 spid168 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 11390 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:00:46.14 spid460 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 1672 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:05.19 spid745 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 18968 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:05.62 spid478 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 406 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:10.03 spid213 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 4390 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:09.95 spid175 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 59906 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:23.48 spid478 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 13515 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:47.55 spid69 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 24047 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:49.81 spid875 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 2265 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:03:12.64 spid168 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 22829 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> The database is about 2.7 gb, and the autogrow is set for 10%. So it was
> trying to add maybe 300 mb, but it kept timing out. I could understand it
> being an issue for a larger number, but 300 mb is not very much.
> When the alter database add filespace happens, is the entire database
> inaccessible during that time? Or is it just the space it is adding that
is
> inaccessible?
> I am wondering if the autogrow timeouts and the user timeouts had the same
> thing happening to make them timeout. Or if the autogrow attempts were
> causing the user timeouts. Seems that cpu on the server were normal, and
no
> other databases had complaints of slows. The autogrow attempts kept
> happening for about an hour and a half before it was succesful. Please help![/vbc
ol]

Autogrow Timeouts

Hello - Can anyone direct me to good articles on Alter database and Autogrow?
We have a shared server where users in one database got timeouts. Looking
back at the logs, the database was trying to grow during that time frame, but
it timed out. Please see part of log:
2006-10-17 15:00:32.97 spid622 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 15922 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:00:44.41 spid168 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 11390 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:00:46.14 spid460 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 1672 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:05.19 spid745 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 18968 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:05.62 spid478 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 406 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:01:10.03 spid213 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 4390 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:09.95 spid175 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 59906 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:23.48 spid478 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 13515 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:47.55 spid69 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 24047 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:02:49.81 spid875 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 2265 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
2006-10-17 15:03:12.64 spid168 Autogrow of file 'SCHR_Data' in database
'SCHR' cancelled or timed out after 22829 ms. Use ALTER DATABASE to set a
smaller FILEGROWTH or to set a new size.
The database is about 2.7 gb, and the autogrow is set for 10%. So it was
trying to add maybe 300 mb, but it kept timing out. I could understand it
being an issue for a larger number, but 300 mb is not very much.
When the alter database add filespace happens, is the entire database
inaccessible during that time? Or is it just the space it is adding that is
inaccessible?
I am wondering if the autogrow timeouts and the user timeouts had the same
thing happening to make them timeout. Or if the autogrow attempts were
causing the user timeouts. Seems that cpu on the server were normal, and no
other databases had complaints of slows. The autogrow attempts kept
happening for about an hour and a half before it was succesful. Please help!Below are two KB about the issue:
http://support.microsoft.com/kb/315512/
INF: Considerations for Autogrow and Autoshrink configuration in SQL
Server
http://support.microsoft.com/kb/305635/
PRB: A Timeout Occurs When a Database Is Automatically Expanding
Mitch wrote:
> Hello - Can anyone direct me to good articles on Alter database and Autogrow?
> We have a shared server where users in one database got timeouts. Looking
> back at the logs, the database was trying to grow during that time frame, but
> it timed out. Please see part of log:
> 2006-10-17 15:00:32.97 spid622 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 15922 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:00:44.41 spid168 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 11390 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:00:46.14 spid460 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 1672 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:05.19 spid745 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 18968 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:05.62 spid478 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 406 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:01:10.03 spid213 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 4390 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:09.95 spid175 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 59906 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:23.48 spid478 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 13515 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:47.55 spid69 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 24047 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:02:49.81 spid875 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 2265 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> 2006-10-17 15:03:12.64 spid168 Autogrow of file 'SCHR_Data' in database
> 'SCHR' cancelled or timed out after 22829 ms. Use ALTER DATABASE to set a
> smaller FILEGROWTH or to set a new size.
> The database is about 2.7 gb, and the autogrow is set for 10%. So it was
> trying to add maybe 300 mb, but it kept timing out. I could understand it
> being an issue for a larger number, but 300 mb is not very much.
> When the alter database add filespace happens, is the entire database
> inaccessible during that time? Or is it just the space it is adding that is
> inaccessible?
> I am wondering if the autogrow timeouts and the user timeouts had the same
> thing happening to make them timeout. Or if the autogrow attempts were
> causing the user timeouts. Seems that cpu on the server were normal, and no
> other databases had complaints of slows. The autogrow attempts kept
> happening for about an hour and a half before it was succesful. Please help!

autogrow speed

Hi,
SQL 2000
We set up all of our databases to autogrow 10%.
Given a 4G database, how fast would 10% autogrowth be?
How can I get notified via e-mail whenever autogrowth occurs?
Would autogrowth be logged in the SQL 2000 server log?
I'd appreciate if anybody can shed some light or point me to the right
direction to figure out myself.
Thanks,
Bing> Given a 4G database, how fast would 10% autogrowth be?
Create a database with a tiny log file and a 400MB size datafile and you will see how your
disksubsystem will perform. Autogrow performs the same way as the initial creation. On my machine
(desktop, 7200 RPM IDE) it took 8 seconds.
> How can I get notified via e-mail whenever autogrowth occurs?
Not easily. You can catch it through a Profiler trace. In order for you to get notified of it, you
would have to use the Profiler GUI and log to a table, on which you have a trigger defined.
> Would autogrowth be logged in the SQL 2000 server log?
No.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bing" <bing@.discussions.microsoft.com> wrote in message
news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> Hi,
> SQL 2000
> We set up all of our databases to autogrow 10%.
> Given a 4G database, how fast would 10% autogrowth be?
> How can I get notified via e-mail whenever autogrowth occurs?
> Would autogrowth be logged in the SQL 2000 server log?
> I'd appreciate if anybody can shed some light or point me to the right
> direction to figure out myself.
> Thanks,
> Bing|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> Hi,
> SQL 2000
> We set up all of our databases to autogrow 10%.
> Given a 4G database, how fast would 10% autogrowth be?
> How can I get notified via e-mail whenever autogrowth occurs?
> Would autogrowth be logged in the SQL 2000 server log?
>
As Tibor says test it.
However, I would NOT use 10% autogrowth.
If you have a 4GB DB, the first time it tries to autogrow, it'll reserve
400MB of disk space.
Next time, 440 MB.
After that 484 MB.
And so on.
So each time it takes longer and longer.
> I'd appreciate if anybody can shed some light or point me to the right
> direction to figure out myself.
> Thanks,
> Bing
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the response. That makes sense. Sounds like using percentage is
not good if autogrowth is performed frequently.
"Greg D. Moore (Strider)" wrote:
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> > Hi,
> >
> > SQL 2000
> >
> > We set up all of our databases to autogrow 10%.
> >
> > Given a 4G database, how fast would 10% autogrowth be?
> > How can I get notified via e-mail whenever autogrowth occurs?
> > Would autogrowth be logged in the SQL 2000 server log?
> >
> As Tibor says test it.
> However, I would NOT use 10% autogrowth.
> If you have a 4GB DB, the first time it tries to autogrow, it'll reserve
> 400MB of disk space.
> Next time, 440 MB.
> After that 484 MB.
> And so on.
> So each time it takes longer and longer.
>
> > I'd appreciate if anybody can shed some light or point me to the right
> > direction to figure out myself.
> >
> > Thanks,
> >
> > Bing
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:B410EBB9-84DA-4557-B978-046FF0099275@.microsoft.com...
> Thanks for the response. That makes sense. Sounds like using percentage
> is
> not good if autogrowth is performed frequently.
>
Actually if you're performing autogrowth with any frequency you probably
have a problem.
You should try to manually manage space and grow it as necessary (if at all)
at slow times.
> "Greg D. Moore (Strider)" wrote:
>>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

autogrow speed

Hi,
SQL 2000
We set up all of our databases to autogrow 10%.
Given a 4G database, how fast would 10% autogrowth be?
How can I get notified via e-mail whenever autogrowth occurs?
Would autogrowth be logged in the SQL 2000 server log?
I'd appreciate if anybody can shed some light or point me to the right
direction to figure out myself.
Thanks,
Bing
> Given a 4G database, how fast would 10% autogrowth be?
Create a database with a tiny log file and a 400MB size datafile and you will see how your
disksubsystem will perform. Autogrow performs the same way as the initial creation. On my machine
(desktop, 7200 RPM IDE) it took 8 seconds.

> How can I get notified via e-mail whenever autogrowth occurs?
Not easily. You can catch it through a Profiler trace. In order for you to get notified of it, you
would have to use the Profiler GUI and log to a table, on which you have a trigger defined.

> Would autogrowth be logged in the SQL 2000 server log?
No.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bing" <bing@.discussions.microsoft.com> wrote in message
news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> Hi,
> SQL 2000
> We set up all of our databases to autogrow 10%.
> Given a 4G database, how fast would 10% autogrowth be?
> How can I get notified via e-mail whenever autogrowth occurs?
> Would autogrowth be logged in the SQL 2000 server log?
> I'd appreciate if anybody can shed some light or point me to the right
> direction to figure out myself.
> Thanks,
> Bing
|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> Hi,
> SQL 2000
> We set up all of our databases to autogrow 10%.
> Given a 4G database, how fast would 10% autogrowth be?
> How can I get notified via e-mail whenever autogrowth occurs?
> Would autogrowth be logged in the SQL 2000 server log?
>
As Tibor says test it.
However, I would NOT use 10% autogrowth.
If you have a 4GB DB, the first time it tries to autogrow, it'll reserve
400MB of disk space.
Next time, 440 MB.
After that 484 MB.
And so on.
So each time it takes longer and longer.

> I'd appreciate if anybody can shed some light or point me to the right
> direction to figure out myself.
> Thanks,
> Bing
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks for the response. That makes sense. Sounds like using percentage is
not good if autogrowth is performed frequently.
"Greg D. Moore (Strider)" wrote:

>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> As Tibor says test it.
> However, I would NOT use 10% autogrowth.
> If you have a 4GB DB, the first time it tries to autogrow, it'll reserve
> 400MB of disk space.
> Next time, 440 MB.
> After that 484 MB.
> And so on.
> So each time it takes longer and longer.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:B410EBB9-84DA-4557-B978-046FF0099275@.microsoft.com...
> Thanks for the response. That makes sense. Sounds like using percentage
> is
> not good if autogrowth is performed frequently.
>
Actually if you're performing autogrowth with any frequency you probably
have a problem.
You should try to manually manage space and grow it as necessary (if at all)
at slow times.
[vbcol=seagreen]
> "Greg D. Moore (Strider)" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

autogrow speed

Hi,
SQL 2000
We set up all of our databases to autogrow 10%.
Given a 4G database, how fast would 10% autogrowth be?
How can I get notified via e-mail whenever autogrowth occurs?
Would autogrowth be logged in the SQL 2000 server log?
I'd appreciate if anybody can shed some light or point me to the right
direction to figure out myself.
Thanks,
Bing> Given a 4G database, how fast would 10% autogrowth be?
Create a database with a tiny log file and a 400MB size datafile and you wil
l see how your
disksubsystem will perform. Autogrow performs the same way as the initial cr
eation. On my machine
(desktop, 7200 RPM IDE) it took 8 seconds.

> How can I get notified via e-mail whenever autogrowth occurs?
Not easily. You can catch it through a Profiler trace. In order for you to g
et notified of it, you
would have to use the Profiler GUI and log to a table, on which you have a t
rigger defined.

> Would autogrowth be logged in the SQL 2000 server log?
No.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bing" <bing@.discussions.microsoft.com> wrote in message
news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> Hi,
> SQL 2000
> We set up all of our databases to autogrow 10%.
> Given a 4G database, how fast would 10% autogrowth be?
> How can I get notified via e-mail whenever autogrowth occurs?
> Would autogrowth be logged in the SQL 2000 server log?
> I'd appreciate if anybody can shed some light or point me to the right
> direction to figure out myself.
> Thanks,
> Bing|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> Hi,
> SQL 2000
> We set up all of our databases to autogrow 10%.
> Given a 4G database, how fast would 10% autogrowth be?
> How can I get notified via e-mail whenever autogrowth occurs?
> Would autogrowth be logged in the SQL 2000 server log?
>
As Tibor says test it.
However, I would NOT use 10% autogrowth.
If you have a 4GB DB, the first time it tries to autogrow, it'll reserve
400MB of disk space.
Next time, 440 MB.
After that 484 MB.
And so on.
So each time it takes longer and longer.

> I'd appreciate if anybody can shed some light or point me to the right
> direction to figure out myself.
> Thanks,
> Bing
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the response. That makes sense. Sounds like using percentage i
s
not good if autogrowth is performed frequently.
"Greg D. Moore (Strider)" wrote:

>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> As Tibor says test it.
> However, I would NOT use 10% autogrowth.
> If you have a 4GB DB, the first time it tries to autogrow, it'll reserve
> 400MB of disk space.
> Next time, 440 MB.
> After that 484 MB.
> And so on.
> So each time it takes longer and longer.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>
>|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:B410EBB9-84DA-4557-B978-046FF0099275@.microsoft.com...
> Thanks for the response. That makes sense. Sounds like using percentage
> is
> not good if autogrowth is performed frequently.
>
Actually if you're performing autogrowth with any frequency you probably
have a problem.
You should try to manually manage space and grow it as necessary (if at all)
at slow times.
[vbcol=seagreen]
> "Greg D. Moore (Strider)" wrote:
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Autogrow question

Hello Folks,

Last evening, as a result of our autogrow settings, our production
database grew from 102 GB to 107 GB. What puzzled me is that, prior to
the growth, we still had a good 10% free space left in the data file.
My understanding is that SQL Server will automatically grow a database
when worker thread decides that more space is necessary to fulfill a
request. We don't suspect the app of sending over a request which
would require that much more space.

Any ideas on why the file grew unexpectedly?

Thanks,
JoeHi

Although everywhere I have found regarding filegrowth indicates it is done
when the file is full, it would make sense that it is grown before it is
actually totally full otherwise you will have problems on a highly active
system!!!

You can still have problems if the file growth is set to very large values
even if the disc space is available as the time to fill up the remainder of
the file may be less than the time taken to grow it.

John

"ceadtinneh" <sulla33@.yahoo.com> wrote in message
news:1128023665.503904.262830@.z14g2000cwz.googlegr oups.com...
> Hello Folks,
> Last evening, as a result of our autogrow settings, our production
> database grew from 102 GB to 107 GB. What puzzled me is that, prior to
> the growth, we still had a good 10% free space left in the data file.
> My understanding is that SQL Server will automatically grow a database
> when worker thread decides that more space is necessary to fulfill a
> request. We don't suspect the app of sending over a request which
> would require that much more space.
> Any ideas on why the file grew unexpectedly?
> Thanks,
> Joe

Autogrow problem

We are seeing an issue with the autogrow setting on some of our production
databases. Occasionally we will see a database fill up, processes trying to
write to the db will show page latch waits and the database will not grow for
up to five hours. The last time it occured, the database only needed to grow
400MB but it did not for hours. Once it did grow most of the writes which
had been waiting did not occur. Their is plenty of space on the disk and the
server was not at capacity. Any ideas?
Thank You.Autogrow is a necessary evil...you should be maintaining your data and log
file sizes based on the expected amount of transactions and altering them
during maintenance hours. Autogrow ought to be a failsafe only due to the
performance impact...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:E6CD323D-2F34-42F6-8EF4-926D2FC69730@.microsoft.com...
> We are seeing an issue with the autogrow setting on some of our production
> databases. Occasionally we will see a database fill up, processes trying
> to
> write to the db will show page latch waits and the database will not grow
> for
> up to five hours. The last time it occured, the database only needed to
> grow
> 400MB but it did not for hours. Once it did grow most of the writes which
> had been waiting did not occur. Their is plenty of space on the disk and
> the
> server was not at capacity. Any ideas?
> Thank You.

Autogrow problem

We are seeing an issue with the autogrow setting on some of our production
databases. Occasionally we will see a database fill up, processes trying to
write to the db will show page latch waits and the database will not grow for
up to five hours. The last time it occured, the database only needed to grow
400MB but it did not for hours. Once it did grow most of the writes which
had been waiting did not occur. Their is plenty of space on the disk and the
server was not at capacity. Any ideas?
Thank You.
Autogrow is a necessary evil...you should be maintaining your data and log
file sizes based on the expected amount of transactions and altering them
during maintenance hours. Autogrow ought to be a failsafe only due to the
performance impact...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:E6CD323D-2F34-42F6-8EF4-926D2FC69730@.microsoft.com...
> We are seeing an issue with the autogrow setting on some of our production
> databases. Occasionally we will see a database fill up, processes trying
> to
> write to the db will show page latch waits and the database will not grow
> for
> up to five hours. The last time it occured, the database only needed to
> grow
> 400MB but it did not for hours. Once it did grow most of the writes which
> had been waiting did not occur. Their is plenty of space on the disk and
> the
> server was not at capacity. Any ideas?
> Thank You.

Autogrow problem

We are seeing an issue with the autogrow setting on some of our production
databases. Occasionally we will see a database fill up, processes trying to
write to the db will show page latch waits and the database will not grow fo
r
up to five hours. The last time it occured, the database only needed to gro
w
400MB but it did not for hours. Once it did grow most of the writes which
had been waiting did not occur. Their is plenty of space on the disk and th
e
server was not at capacity. Any ideas?
Thank You.Autogrow is a necessary evil...you should be maintaining your data and log
file sizes based on the expected amount of transactions and altering them
during maintenance hours. Autogrow ought to be a failsafe only due to the
performance impact...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Len Gustafson" <LenGustafson@.discussions.microsoft.com> wrote in message
news:E6CD323D-2F34-42F6-8EF4-926D2FC69730@.microsoft.com...
> We are seeing an issue with the autogrow setting on some of our production
> databases. Occasionally we will see a database fill up, processes trying
> to
> write to the db will show page latch waits and the database will not grow
> for
> up to five hours. The last time it occured, the database only needed to
> grow
> 400MB but it did not for hours. Once it did grow most of the writes which
> had been waiting did not occur. Their is plenty of space on the disk and
> the
> server was not at capacity. Any ideas?
> Thank You.

autogrow of log file possibly cause timeout?

i had a problem with a server instance where an app processing a file and putting the data into the database using a stored procedure was timing out. the app is a service and in the case of errors, will try to process the file until it has been succesfully added data to the database. in the event viewer i keep seeing this message during this timeout period:

"Autogrow of file 'mydatabase_log' in database 'mydatabase' was cancelled by user or timed out after 15687 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size."

is there a possibility that this process was causing my SP to timeout? are there any other log or debug files that SQL Express creates that might be helpful in discovering what caused the timeout? currently the log is at... ouch 10gigs restricted... maybe trying to autogrow from this while restricted is hogging resources?

As SQL Server Express databases are limited to 4GB per database, I just wonder why you need 10GB of logs ? Did you consider backing up your database and shrink your logfiles ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||when we created the database, we used the default setting for the log file, which was "By 10 percent, restricted growth ". currently we backed up the database, detached the DB, removed the old log file, and reattached the DB. it created a new log file and we set it to a smaller size. as i understand it the log file holds the uncommitted transactions, is this true? if we want the log file to stay at a certain size maybe i need to uncheck AutoGrowth? and going back to the original question, if SQL was trying to autogrow a 10 gig file, could this have used all the resources at the moment and timeout any stored procedures running during this autogrowth process?|||

Hi nattylife,

for an instance assump your Log File size is 12 GB, you have set up Auto Growth option in 20% , now when ever your T-Log file need to grow it will grow 12 GB * 20 % means it will grow 2.4 GB in size and while this process is running it occupy the server resource (yes it will effect while this happens in pick time).

If you stop/uncheck/disabled Auto Grow , you may be in trouble because if your T-log need to grow and Auto Grow is disabled it might be resulted in SUSPECT status of your database, so don't disabled Auto Grow set it in appropriate % / MB as per your *requirement/perdiction* in growth of your database. BTW what is your Recovery Model of your database?

Refer T-Log architecture in BOL to understand more, BOL is your best friend.

Hemantgiri S. Goswami

|||

Your way of removing the log file is dangerous and not best practise. You should take a backup of your database and shrink the log afterwards, rather than just *deleting* it and let it recreate by SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Your way of removing the log file is dangerous and not best practise. You should take a backup of your database and shrink the log afterwards, rather than just *deleting* it and let it recreate by SQL Server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

we backed up the database before we do any questionable practices. the database is filled with 3 text files that our app recieves every day. inside each file are the records for the database. we archive these every day for 90 days in case we have any database issues. after doing some more research, i found how to shrink the log in the CTP which was the approach i was looking for, just didnt find until after the fact. i didnt realize that this log file would grow coninuously, i was under the assumption it would eventually get written over. so we are discussing a way to archive the ldf file now too.

|||

Hi,

you should consider using the backup functions of SQL Server. You are able to do either full / differential or transaction log backups. A combination of those will help you to make your databases disaster-recoverable.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||i checked the backup options in the CTP. i see where i can do a log backup, but is this a manual process or when will it do it on a regular basis since i dont see any option to automate it. if i wanted to automate it, would i need to implement this with my own service or such?|||Hi,

SQL Server Agent is not shipped with SQL Server Express. An approach could be to script out the backup command (using the functionality of the management studio) and schedule the execution of the script using an AT command (or any other scheduler) by executing the script with SQLCMD.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Autogrow of log

I get this info message in my NT log:
Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
by user or timed out after 546 milliseconds. Use ALTER DATABASE to
set a smaller FILEGROWTH value for this file or to explicitly set a
new file size.
This database is set to autogrow by 10%.
Searching the net, a few have suggested increasing the 10% to a larger
value. But it seems that the error is SQL Server failing to grow the
file in a timely manner. So moving from 10% to larger number will
only make matters worse. If I set it to a fixed size, what if that is
not enough?
What is the correct approach?
You don't want to set it larger you want to change it to a fixed size that
you know will grow in well under the timeout period. Once the files get
beyond 100MB or so you should change it to a fixed size of growth.
Andrew J. Kelly SQL MVP
"pbx" <pbeisel@.gmail.com> wrote in message
news:1183851948.581194.56800@.i38g2000prf.googlegro ups.com...
> I get this info message in my NT log:
> Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
> by user or timed out after 546 milliseconds. Use ALTER DATABASE to
> set a smaller FILEGROWTH value for this file or to explicitly set a
> new file size.
> This database is set to autogrow by 10%.
> Searching the net, a few have suggested increasing the 10% to a larger
> value. But it seems that the error is SQL Server failing to grow the
> file in a timely manner. So moving from 10% to larger number will
> only make matters worse. If I set it to a fixed size, what if that is
> not enough?
> What is the correct approach?
>
|||Monitor how your database files grow. This will give you an idea on what
value to specify for autogrow increments. This is critical especially when
the files are very big (imagine 10% autogrow increments for 100GB database
files)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237Qq9gQwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> You don't want to set it larger you want to change it to a fixed size that
> you know will grow in well under the timeout period. Once the files get
> beyond 100MB or so you should change it to a fixed size of growth.
> --
> Andrew J. Kelly SQL MVP
> "pbx" <pbeisel@.gmail.com> wrote in message
> news:1183851948.581194.56800@.i38g2000prf.googlegro ups.com...
>

Autogrow of log

I get this info message in my NT log:
Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
by user or timed out after 546 milliseconds. Use ALTER DATABASE to
set a smaller FILEGROWTH value for this file or to explicitly set a
new file size.
This database is set to autogrow by 10%.
Searching the net, a few have suggested increasing the 10% to a larger
value. But it seems that the error is SQL Server failing to grow the
file in a timely manner. So moving from 10% to larger number will
only make matters worse. If I set it to a fixed size, what if that is
not enough?
What is the correct approach?You don't want to set it larger you want to change it to a fixed size that
you know will grow in well under the timeout period. Once the files get
beyond 100MB or so you should change it to a fixed size of growth.
Andrew J. Kelly SQL MVP
"pbx" <pbeisel@.gmail.com> wrote in message
news:1183851948.581194.56800@.i38g2000prf.googlegroups.com...
> I get this info message in my NT log:
> Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
> by user or timed out after 546 milliseconds. Use ALTER DATABASE to
> set a smaller FILEGROWTH value for this file or to explicitly set a
> new file size.
> This database is set to autogrow by 10%.
> Searching the net, a few have suggested increasing the 10% to a larger
> value. But it seems that the error is SQL Server failing to grow the
> file in a timely manner. So moving from 10% to larger number will
> only make matters worse. If I set it to a fixed size, what if that is
> not enough?
> What is the correct approach?
>|||Monitor how your database files grow. This will give you an idea on what
value to specify for autogrow increments. This is critical especially when
the files are very big (imagine 10% autogrow increments for 100GB database
files)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237Qq9gQwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> You don't want to set it larger you want to change it to a fixed size that
> you know will grow in well under the timeout period. Once the files get
> beyond 100MB or so you should change it to a fixed size of growth.
> --
> Andrew J. Kelly SQL MVP
> "pbx" <pbeisel@.gmail.com> wrote in message
> news:1183851948.581194.56800@.i38g2000prf.googlegroups.com...
>

Autogrow of log

I get this info message in my NT log:
Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
by user or timed out after 546 milliseconds. Use ALTER DATABASE to
set a smaller FILEGROWTH value for this file or to explicitly set a
new file size.
This database is set to autogrow by 10%.
Searching the net, a few have suggested increasing the 10% to a larger
value. But it seems that the error is SQL Server failing to grow the
file in a timely manner. So moving from 10% to larger number will
only make matters worse. If I set it to a fixed size, what if that is
not enough?
What is the correct approach?You don't want to set it larger you want to change it to a fixed size that
you know will grow in well under the timeout period. Once the files get
beyond 100MB or so you should change it to a fixed size of growth.
--
Andrew J. Kelly SQL MVP
"pbx" <pbeisel@.gmail.com> wrote in message
news:1183851948.581194.56800@.i38g2000prf.googlegroups.com...
> I get this info message in my NT log:
> Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
> by user or timed out after 546 milliseconds. Use ALTER DATABASE to
> set a smaller FILEGROWTH value for this file or to explicitly set a
> new file size.
> This database is set to autogrow by 10%.
> Searching the net, a few have suggested increasing the 10% to a larger
> value. But it seems that the error is SQL Server failing to grow the
> file in a timely manner. So moving from 10% to larger number will
> only make matters worse. If I set it to a fixed size, what if that is
> not enough?
> What is the correct approach?
>|||Monitor how your database files grow. This will give you an idea on what
value to specify for autogrow increments. This is critical especially when
the files are very big (imagine 10% autogrow increments for 100GB database
files)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237Qq9gQwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> You don't want to set it larger you want to change it to a fixed size that
> you know will grow in well under the timeout period. Once the files get
> beyond 100MB or so you should change it to a fixed size of growth.
> --
> Andrew J. Kelly SQL MVP
> "pbx" <pbeisel@.gmail.com> wrote in message
> news:1183851948.581194.56800@.i38g2000prf.googlegroups.com...
>> I get this info message in my NT log:
>> Autogrow of file 'w2publish_Log' in database 'w2publish' was cancelled
>> by user or timed out after 546 milliseconds. Use ALTER DATABASE to
>> set a smaller FILEGROWTH value for this file or to explicitly set a
>> new file size.
>> This database is set to autogrow by 10%.
>> Searching the net, a few have suggested increasing the 10% to a larger
>> value. But it seems that the error is SQL Server failing to grow the
>> file in a timely manner. So moving from 10% to larger number will
>> only make matters worse. If I set it to a fixed size, what if that is
>> not enough?
>> What is the correct approach?
>

Autogrow of file FORMS in database FORMS cancelled or timed out after 30547 ms.

Afternoon

I'm getting the below error message:

Autogrow of file 'FORMS' in database 'FORMS' cancelled or timed out after 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.

FORMS.LDF file is 7613952 KB and the growth is 512MB .

By how much should I set the filegrowth? The users are complaining that the application is freezing on them.

This is sqlserver 2000.Have you checked to see if the disk that the LDF file on has enough space left to grow? Is your databse in full recovery mode without a tran log backup EVER taking place? Do you need it in full recovery mode, or will simple do? Do you have a long running transaction filling up the log file?|||hi

There is nearly 2GB of free space on the server and the database is set to full recovery mode as it needs to be. There is only 1 transcation log being created and it's filling up day by day. Currently the users are unable to login.|||Are you doing transaction log backups?|||I changed the recovery model to simple and it managed to free up some space, I then changed it back to full and limited the space. All seem's to be working.|||With the database in full recovery mode, you will have to do periodic log backups in order to allow sqsl server to truncate and reuse log file segments.

If you do not institute tran log backups, you will run into the same problem as you had when you started this exercise ... the log file will fill up, autogrow, and continue until there is no more disk space for it to grow.

BTW ... I hope you did a full database backup after you did the change to simple and back to full, because you lost the ability to restore to a point in time with transaction log backups ... oh that's right ... you aren't doing log backups. So you will have lost all changes since your last full backup.

For a database in full recovery mode, best practice is to do a full backup periodically, and transaction log backups on a regular basis until you do another full backup. That helps keep the transaction log from growing out of control, and allows you to restore to a point in time if neccessary.|||Yes I did a full database backup after I made the changes.

I have not lost any data the last database backup was done last night as the database and the server gets backed up every night. The users have not made any changes for the past 5 days.

I will def look into a long term fix for this.

autogrow history

Hi,
Is there a way to see when autogrow has been triggered for a particular database/tran log? Over the weekend, we ran out of disk space on a server that has multiple databases, and I'd like to find out which database's tran log was at fault.
Thanks,
SusanThis kind of error should be logged on SQL Server Logs.

Originally posted by dbadba
Hi,

Is there a way to see when autogrow has been triggered for a particular database/tran log? Over the weekend, we ran out of disk space on a server that has multiple databases, and I'd like to find out which database's tran log was at fault.

Thanks,
Susan|||Thanks for the reply!

There is an "out of space" error in the log, but I'm looking for a log (or alert, etc.) that would track each time autogrow was triggered, even when it didn't result in an error. Do you know where I can find that information?

Thanks,
Laura|||Laura,

As far as I know, there is no way to create an alert based on autogrow event, unless you fix a log size limit and then add an alert using "error 9002, severity 19 - The log file for database xxxx is full". I think there is even a template in SQL 2000 Alerts for that.
Another approach would be to define a trace based on transaction log autogrow event. You can use SQL Profiler for tracing; or it can generates the SQL script for you, and then you can include it in a stored procedure that can be scheduled to start at a specific time.

Hope it helps !

Originally posted by dbadba
Thanks for the reply!

There is an "out of space" error in the log, but I'm looking for a log (or alert, etc.) that would track each time autogrow was triggered, even when it didn't result in an error. Do you know where I can find that information?

Thanks,
Laura|||Thanks! I will check into both of the options you suggested.|||if you want to prevent this kind of error from happening you can also create an alert that is triggered when a log is more than a certain percentage full.|||Thank you very much for the suggestion! I appreciate it.|||UNtested but this could be run regularly to email you when the log is over 50% full

create procedure mylog @.dbname varchar(50)
as
declare @.logspace int
, @.wo_num varchar(30) , @.deleted_user varchar(255),@.emails varchar(255) , @.messages varchar(100),
@.subjecttext varchar(100)
truncate table tempdb..logspace
insert tempdb..logspace (dbname,logsize,percentused,status)
execute('dbcc perflog')
set @.logspace = (select percentused from tempdb..logspace where dbname = @.dbname)
print @.logspace
if @.logspace > 50
set @.messages = 'Log Space is over 50%'
set @.subjecttext = @.dbname + 'log is over 50% full'
set @.emails = 'joesmoe@.kokomo.com'
exec master..xp_sendmail @.recipients = @.emails , @.message = @.messages,@.subject = @.subjecttext
else
return

HTH

AutoGrow DB option

Hi all, what i really want to know is what actualy tells sql server to grow
the database, file, i found a post that says something about xmb size, what
can find no other impormation, So my question is what event promts sql to
autogrow its database file,
Thanks Andrew
Whenever a connection is executing a query where space needed to be allocated (read about extents in
Books Online), and there is not space in the database file (or files if that object is on a
filegroup constituted of several files).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:uYmvtb3ZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Hi all, what i really want to know is what actualy tells sql server to grow the database, file, i
> found a post that says something about xmb size, what can find no other impormation, So my
> question is what event promts sql to autogrow its database file,
> Thanks Andrew
>

AutoGrow DB option

Hi all, what i really want to know is what actualy tells sql server to grow
the database, file, i found a post that says something about xmb size, what
can find no other impormation, So my question is what event promts sql to
autogrow its database file,
Thanks AndrewWhenever a connection is executing a query where space needed to be allocate
d (read about extents in
Books Online), and there is not space in the database file (or files if that
object is on a
filegroup constituted of several files).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:uYmvtb3ZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Hi all, what i really want to know is what actualy tells sql server to gro
w the database, file, i
> found a post that says something about xmb size, what can find no other im
pormation, So my
> question is what event promts sql to autogrow its database file,
> Thanks Andrew
>