Autogrowth values keep changing

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||

Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...

John

|||

Not a helpful reply, it can and it does.

I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.

This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.

This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.

|||

Yes this is quite serious. It stops our ETL-process because of the drive being full.

Nothing in our tracelog yet...

|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

|||

Thanks!

BR John

|||

HowardRichards wrote:

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

I was not aware of this bug. Thanks for the correction.|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!

Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.

|||

Yes - we've been hit by this as well.

We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.

The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!

So you need to change it using the alter database statement:

alter database dbname modify file (name = filename, filegrowth = 10%)

Roll on SP2...

|||

We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.

When attempting to change the value, the following error appears:

Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)

from the SQL Server 2005 SP2 fix list:

919611 (http://support.microsoft.com/kb/919611/)

FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1

Can anyone advise if running this statement will correct this issue permanently?

alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)

Thanks!

|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.

Autogrowth values keep changing

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||

Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...

John

|||

Not a helpful reply, it can and it does.

I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.

This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.

This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.

|||

Yes this is quite serious. It stops our ETL-process because of the drive being full.

Nothing in our tracelog yet...

|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

|||

Thanks!

BR John

|||

HowardRichards wrote:

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

I was not aware of this bug. Thanks for the correction.|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!

Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.

|||

Yes - we've been hit by this as well.

We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.

The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!

So you need to change it using the alter database statement:

alter database dbname modify file (name = filename, filegrowth = 10%)

Roll on SP2...

|||

We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.

When attempting to change the value, the following error appears:

Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)

from the SQL Server 2005 SP2 fix list:

919611 (http://support.microsoft.com/kb/919611/)

FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1

Can anyone advise if running this statement will correct this issue permanently?

alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)

Thanks!

|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.

Autogrowth values keep changing

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||

Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...

John

|||

Not a helpful reply, it can and it does.

I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.

This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.

This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.

|||

Yes this is quite serious. It stops our ETL-process because of the drive being full.

Nothing in our tracelog yet...

|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

|||

Thanks!

BR John

|||

HowardRichards wrote:

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

I was not aware of this bug. Thanks for the correction.|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!

Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.

|||

Yes - we've been hit by this as well.

We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.

The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!

So you need to change it using the alter database statement:

alter database dbname modify file (name = filename, filegrowth = 10%)

Roll on SP2...

|||

We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.

When attempting to change the value, the following error appears:

Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)

from the SQL Server 2005 SP2 fix list:

919611 (http://support.microsoft.com/kb/919611/) FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1

Can anyone advise if running this statement will correct this issue permanently?

alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)

Thanks!

|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.

Autogrowth values keep changing

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||

Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...

John

|||

Not a helpful reply, it can and it does.

I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.

This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.

This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.

|||

Yes this is quite serious. It stops our ETL-process because of the drive being full.

Nothing in our tracelog yet...

|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

|||

Thanks!

BR John

|||

HowardRichards wrote:

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

I was not aware of this bug. Thanks for the correction.|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!

Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.

|||

Yes - we've been hit by this as well.

We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.

The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!

So you need to change it using the alter database statement:

alter database dbname modify file (name = filename, filegrowth = 10%)

Roll on SP2...

|||

We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.

When attempting to change the value, the following error appears:

Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)

from the SQL Server 2005 SP2 fix list:

919611 (http://support.microsoft.com/kb/919611/)

FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1

Can anyone advise if running this statement will correct this issue permanently?

alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)

Thanks!

|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.

Autogrowth values keep changing

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||

Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...

John

|||

Not a helpful reply, it can and it does.

I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.

This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.

This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.

|||

Yes this is quite serious. It stops our ETL-process because of the drive being full.

Nothing in our tracelog yet...

|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

|||

Thanks!

BR John

|||

HowardRichards wrote:

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/

Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

I was not aware of this bug. Thanks for the correction.|||

It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!

Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.

|||

Yes - we've been hit by this as well.

We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.

The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!

So you need to change it using the alter database statement:

alter database dbname modify file (name = filename, filegrowth = 10%)

Roll on SP2...

|||

We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.

When attempting to change the value, the following error appears:

Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)

from the SQL Server 2005 SP2 fix list:

919611 (http://support.microsoft.com/kb/919611/)

FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1

Can anyone advise if running this statement will correct this issue permanently?

alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)

Thanks!

|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.

Autogrowth for MDF and LDF files

I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?

Thanks,

CarlosI have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?

Thanks,

Carlos

I generally allow dbs that are smaller that 20 GB to autogrow by the default setting (10%). But once they get above that size, I manage them manually and ensure that there is enough empty space in the datafile to get through until the next maintenance window.

Growing a data file in SQL 2005 is not as expensive (IO wise) as it was in SQL 2000, but I still think you want to keep a closer eye on things once they get above 20 GB. 20 GB is admittedly arbitrary. If you have space issues, you might consider a lower threshold.

Regards,

hmscott|||It depends on what your database is used for. Mine are configured to grow by several hundred MB\ a few GB but that is because there are small numbers of massive modifications. An OLTP database should not, IMHO, be growing that much each time. The user that submitted the modification that triggers a 500MB growth could be twiddling their thumbs for quite some time cursing the system as they do.

I too would manage the growth at peak periods with a view to eliminating\ reducing autogrowth as much as possible.|||Your file growth is because of your 500MB setting. When SQL starts to run out of space it will adjust by 500MB. If has to adjust twice a day, there's your 1GB. I think you're fine where you're at. Just make sure you have enough drive space. I'm sure your growth will plateau.

Autogrowth automatically set to 640 percent

Hello,
I've configured SQL Server 2005 express to use autogrowth by 5mb.
After a few weeks the autogrowth option automatically switched to 640
percent what will lead to performance problems.
After is set back the autogrowth to 5mb, a few weeks later the same
problem occurs.
Does anyone know what can cause this problem?
Thanks in advance,
PimWhich servicepack is installed?
You can disable autogrow and set the database size manually as temporarly
workaround.
greetz
"Pim75" wrote:
> Hello,
> I've configured SQL Server 2005 express to use autogrowth by 5mb.
> After a few weeks the autogrowth option automatically switched to 640
> percent what will lead to performance problems.
> After is set back the autogrowth to 5mb, a few weeks later the same
> problem occurs.
> Does anyone know what can cause this problem?
> Thanks in advance,
> Pim
>|||hi
make sure no one else is playing with the server
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Pim75" <p.megens@.tiscali.nl> wrote in message
news:1181732495.637264.178440@.d30g2000prg.googlegroups.com...
> Hello,
> I've configured SQL Server 2005 express to use autogrowth by 5mb.
> After a few weeks the autogrowth option automatically switched to 640
> percent what will lead to performance problems.
> After is set back the autogrowth to 5mb, a few weeks later the same
> problem occurs.
> Does anyone know what can cause this problem?
> Thanks in advance,
> Pim
>|||We use Microsoft SQL Server Express Edition, version 9.00.1399.06
Windows 2003 webserver edition, servicepack 1.
We have some more servers but this is the only one where the problem
occurs.
On 13 jun, 13:37, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:
> Which servicepack is installed?
> You can disable autogrow and set the database size manually as temporarly
> workaround.
> greetz
>
> "Pim75" wrote:
> > Hello,
> > I've configured SQL Server 2005 express to use autogrowth by 5mb.
> > After a few weeks the autogrowth option automatically switched to 640
> > percent what will lead to performance problems.
> > After is set back the autogrowth to 5mb, a few weeks later the same
> > problem occurs.
> > Does anyone know what can cause this problem?
> > Thanks in advance,
> > Pim- Tekst uit oorspronkelijk bericht niet weergeven -
> - Tekst uit oorspronkelijk bericht weergeven -|||Pim75 wrote:
> Hello,
> I've configured SQL Server 2005 express to use autogrowth by 5mb.
> After a few weeks the autogrowth option automatically switched to 640
> percent what will lead to performance problems.
> After is set back the autogrowth to 5mb, a few weeks later the same
> problem occurs.
> Does anyone know what can cause this problem?
> Thanks in advance,
> Pim
>
Hi
Not that it helps you a lot, but we see the same thing happening on one
specific database. Occasionally the mdf file for this database is being
set to a growth rate of 12800% and then the file is around 258 GB.
This has started after we moved this database from a SQL2000 server to a
SQL2005 server. The vendor knows about the issue and they has the same
problem with a few other customers. Since it happens quite seldom, they
haven't been able to figure out what it is that causes it to happen. As
a temporary workaround, I'v written a little sql script that runs every
15 minuttes to check the filesize and filegrowth. If one of them has
changed since last run it sends me an email. I can then go in an correct
the values.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Personally I would just set the job to issue the necessary ALTER DATABASE
statement to fix the growth increment when identified (as well as send an
email that it had been done).
Also, I didn't investigate, but it may be possible to use a DDL trigger to
trap ALTER DATABASE calls on this database to 1) prevent the growth factor
change and 2) identify the offending code and get it fixed.
--
TheSQLGuru
President
Indicium Resources, Inc.
""Steen Schlüter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:%23lpkSXbrHHA.1296@.TK2MSFTNGP06.phx.gbl...
> Pim75 wrote:
>> Hello,
>> I've configured SQL Server 2005 express to use autogrowth by 5mb.
>> After a few weeks the autogrowth option automatically switched to 640
>> percent what will lead to performance problems.
>> After is set back the autogrowth to 5mb, a few weeks later the same
>> problem occurs.
>> Does anyone know what can cause this problem?
>> Thanks in advance,
>> Pim
> Hi
> Not that it helps you a lot, but we see the same thing happening on one
> specific database. Occasionally the mdf file for this database is being
> set to a growth rate of 12800% and then the file is around 258 GB.
> This has started after we moved this database from a SQL2000 server to a
> SQL2005 server. The vendor knows about the issue and they has the same
> problem with a few other customers. Since it happens quite seldom, they
> haven't been able to figure out what it is that causes it to happen. As a
> temporary workaround, I'v written a little sql script that runs every 15
> minuttes to check the filesize and filegrowth. If one of them has changed
> since last run it sends me an email. I can then go in an correct the
> values.
>
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator

Autogrowth automatically set to 640 percent

Hello,
I've configured SQL Server 2005 express to use autogrowth by 5mb.
After a few weeks the autogrowth option automatically switched to 640
percent what will lead to performance problems.
After is set back the autogrowth to 5mb, a few weeks later the same
problem occurs.
Does anyone know what can cause this problem?
Thanks in advance,
Pim
Which servicepack is installed?
You can disable autogrow and set the database size manually as temporarly
workaround.
greetz
"Pim75" wrote:

> Hello,
> I've configured SQL Server 2005 express to use autogrowth by 5mb.
> After a few weeks the autogrowth option automatically switched to 640
> percent what will lead to performance problems.
> After is set back the autogrowth to 5mb, a few weeks later the same
> problem occurs.
> Does anyone know what can cause this problem?
> Thanks in advance,
> Pim
>
|||We use Microsoft SQL Server Express Edition, version 9.00.1399.06
Windows 2003 webserver edition, servicepack 1.
We have some more servers but this is the only one where the problem
occurs.
On 13 jun, 13:37, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:
> Which servicepack is installed?
> You can disable autogrow and set the database size manually as temporarly
> workaround.
> greetz
>
> "Pim75" wrote:
>
>
> - Tekst uit oorspronkelijk bericht weergeven -
|||Pim75 wrote:
> Hello,
> I've configured SQL Server 2005 express to use autogrowth by 5mb.
> After a few weeks the autogrowth option automatically switched to 640
> percent what will lead to performance problems.
> After is set back the autogrowth to 5mb, a few weeks later the same
> problem occurs.
> Does anyone know what can cause this problem?
> Thanks in advance,
> Pim
>
Hi
Not that it helps you a lot, but we see the same thing happening on one
specific database. Occasionally the mdf file for this database is being
set to a growth rate of 12800% and then the file is around 258 GB.
This has started after we moved this database from a SQL2000 server to a
SQL2005 server. The vendor knows about the issue and they has the same
problem with a few other customers. Since it happens quite seldom, they
haven't been able to figure out what it is that causes it to happen. As
a temporary workaround, I'v written a little sql script that runs every
15 minuttes to check the filesize and filegrowth. If one of them has
changed since last run it sends me an email. I can then go in an correct
the values.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||Personally I would just set the job to issue the necessary ALTER DATABASE
statement to fix the growth increment when identified (as well as send an
email that it had been done).
Also, I didn't investigate, but it may be possible to use a DDL trigger to
trap ALTER DATABASE calls on this database to 1) prevent the growth factor
change and 2) identify the offending code and get it fixed.
TheSQLGuru
President
Indicium Resources, Inc.
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:%23lpkSXbrHHA.1296@.TK2MSFTNGP06.phx.gbl...
> Pim75 wrote:
> Hi
> Not that it helps you a lot, but we see the same thing happening on one
> specific database. Occasionally the mdf file for this database is being
> set to a growth rate of 12800% and then the file is around 258 GB.
> This has started after we moved this database from a SQL2000 server to a
> SQL2005 server. The vendor knows about the issue and they has the same
> problem with a few other customers. Since it happens quite seldom, they
> haven't been able to figure out what it is that causes it to happen. As a
> temporary workaround, I'v written a little sql script that runs every 15
> minuttes to check the filesize and filegrowth. If one of them has changed
> since last run it sends me an email. I can then go in an correct the
> values.
>
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator

Autogrowth automatically set to 640 percent

Hello,
I've configured SQL Server 2005 express to use autogrowth by 5mb.
After a few weeks the autogrowth option automatically switched to 640
percent what will lead to performance problems.
After is set back the autogrowth to 5mb, a few weeks later the same
problem occurs.
Does anyone know what can cause this problem?
Thanks in advance,
PimWhich servicepack is installed?
You can disable autogrow and set the database size manually as temporarly
workaround.
greetz
"Pim75" wrote:

> Hello,
> I've configured SQL Server 2005 express to use autogrowth by 5mb.
> After a few weeks the autogrowth option automatically switched to 640
> percent what will lead to performance problems.
> After is set back the autogrowth to 5mb, a few weeks later the same
> problem occurs.
> Does anyone know what can cause this problem?
> Thanks in advance,
> Pim
>|||hi
make sure no one else is playing with the server
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Pim75" <p.megens@.tiscali.nl> wrote in message
news:1181732495.637264.178440@.d30g2000prg.googlegroups.com...
> Hello,
> I've configured SQL Server 2005 express to use autogrowth by 5mb.
> After a few weeks the autogrowth option automatically switched to 640
> percent what will lead to performance problems.
> After is set back the autogrowth to 5mb, a few weeks later the same
> problem occurs.
> Does anyone know what can cause this problem?
> Thanks in advance,
> Pim
>|||We use Microsoft SQL Server Express Edition, version 9.00.1399.06
Windows 2003 webserver edition, servicepack 1.
We have some more servers but this is the only one where the problem
occurs.
On 13 jun, 13:37, Hate_orphaned_users
<Hateorphanedus...@.discussions.microsoft.com> wrote:
> Which servicepack is installed?
> You can disable autogrow and set the database size manually as temporarly
> workaround.
> greetz
>
> "Pim75" wrote:
>
>
>
> - Tekst uit oorspronkelijk bericht weergeven -|||Pim75 wrote:
> Hello,
> I've configured SQL Server 2005 express to use autogrowth by 5mb.
> After a few weeks the autogrowth option automatically switched to 640
> percent what will lead to performance problems.
> After is set back the autogrowth to 5mb, a few weeks later the same
> problem occurs.
> Does anyone know what can cause this problem?
> Thanks in advance,
> Pim
>
Hi
Not that it helps you a lot, but we see the same thing happening on one
specific database. Occasionally the mdf file for this database is being
set to a growth rate of 12800% and then the file is around 258 GB.
This has started after we moved this database from a SQL2000 server to a
SQL2005 server. The vendor knows about the issue and they has the same
problem with a few other customers. Since it happens quite seldom, they
haven't been able to figure out what it is that causes it to happen. As
a temporary workaround, I'v written a little sql script that runs every
15 minuttes to check the filesize and filegrowth. If one of them has
changed since last run it sends me an email. I can then go in an correct
the values.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Personally I would just set the job to issue the necessary ALTER DATABASE
statement to fix the growth increment when identified (as well as send an
email that it had been done).
Also, I didn't investigate, but it may be possible to use a DDL trigger to
trap ALTER DATABASE calls on this database to 1) prevent the growth factor
change and 2) identify the offending code and get it fixed.
TheSQLGuru
President
Indicium Resources, Inc.
""Steen Schlter Persson (DK)"" <steen@.REMOVE_THIS_asavaenget.dk> wrote in
message news:%23lpkSXbrHHA.1296@.TK2MSFTNGP06.phx.gbl...
> Pim75 wrote:
> Hi
> Not that it helps you a lot, but we see the same thing happening on one
> specific database. Occasionally the mdf file for this database is being
> set to a growth rate of 12800% and then the file is around 258 GB.
> This has started after we moved this database from a SQL2000 server to a
> SQL2005 server. The vendor knows about the issue and they has the same
> problem with a few other customers. Since it happens quite seldom, they
> haven't been able to figure out what it is that causes it to happen. As a
> temporary workaround, I'v written a little sql script that runs every 15
> minuttes to check the filesize and filegrowth. If one of them has changed
> since last run it sends me an email. I can then go in an correct the
> values.
>
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator

Autogrowth 32000 percent

I have a database with a 3G datafile, and 1G logfile. I have set the
Autogrowth on the datafile to 250M, for the third time. Somehow, I don't know
when, the Autogrowth is getting changed to [32000 percent]. So when the
datafile tries to expand it take a considerable amount of disk space (106G),
then my log dumps start failing due to low disk space.
Autogrowth=32000%
Has anyone seen this before?
Thanks in advance,
KenL wrote:
> I have a database with a 3G datafile, and 1G logfile. I have set the
> Autogrowth on the datafile to 250M, for the third time. Somehow, I don't know
> when, the Autogrowth is getting changed to [32000 percent]. So when the
> datafile tries to expand it take a considerable amount of disk space (106G),
> then my log dumps start failing due to low disk space.
> Autogrowth=32000%
> Has anyone seen this before?
> Thanks in advance,
You don't say, but I'm assuming this is on SQL 2005? This is a known
bug:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Tracy, thanks very much for the response. The feedback referenced below is
talking about SQL 2000, and says it will be fixed in the next release of SQL
Server. I am using SQL 2005, so isn't that the next release? I do not see a
resolution?
Thanks,
"Tracy McKibben" wrote:

> KenL wrote:
> You don't say, but I'm assuming this is on SQL 2005? This is a known
> bug:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||KenL wrote:
> Tracy, thanks very much for the response. The feedback referenced below is
> talking about SQL 2000, and says it will be fixed in the next release of SQL
> Server. I am using SQL 2005, so isn't that the next release? I do not see a
> resolution?
No, this is definately a SQL 2005 bug... The article that I linked to
talks about one possible cause of this as being a status bit in a
converted SQL 2000 database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Tracy, again thanks for the response.
The database is in SQL 2005, and was upgraded from a SQL 7 to SQL 2000, and
then SQL 2005. So are your saying this is a known bug that there currently is
no fix or workaround?
Thanks,
Ken
"Tracy McKibben" wrote:

> KenL wrote:
> No, this is definately a SQL 2005 bug... The article that I linked to
> talks about one possible cause of this as being a status bit in a
> converted SQL 2000 database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Autogrowth 32000 percent

I have a database with a 3G datafile, and 1G logfile. I have set the
Autogrowth on the datafile to 250M, for the third time. Somehow, I don't kno
w
when, the Autogrowth is getting changed to [32000 percent]. So when the
datafile tries to expand it take a considerable amount of disk space (106G),
then my log dumps start failing due to low disk space.
Autogrowth=32000%
Has anyone seen this before?
Thanks in advance,KenL wrote:
> I have a database with a 3G datafile, and 1G logfile. I have set the
> Autogrowth on the datafile to 250M, for the third time. Somehow, I don't k
now
> when, the Autogrowth is getting changed to [32000 percent]. So when th
e
> datafile tries to expand it take a considerable amount of disk space (106G
),
> then my log dumps start failing due to low disk space.
> Autogrowth=32000%
> Has anyone seen this before?
> Thanks in advance,
You don't say, but I'm assuming this is on SQL 2005? This is a known
bug:
http://connect.microsoft.com/SQLSer...=12717
7
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy, thanks very much for the response. The feedback referenced below is
talking about SQL 2000, and says it will be fixed in the next release of SQL
Server. I am using SQL 2005, so isn't that the next release? I do not see a
resolution?
Thanks,
"Tracy McKibben" wrote:

> KenL wrote:
> You don't say, but I'm assuming this is on SQL 2005? This is a known
> bug:
> http://connect.microsoft.com/SQLSer...=127
177
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||KenL wrote:
> Tracy, thanks very much for the response. The feedback referenced below is
> talking about SQL 2000, and says it will be fixed in the next release of S
QL
> Server. I am using SQL 2005, so isn't that the next release? I do not see
a
> resolution?
No, this is definately a SQL 2005 bug... The article that I linked to
talks about one possible cause of this as being a status bit in a
converted SQL 2000 database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy, again thanks for the response.
The database is in SQL 2005, and was upgraded from a SQL 7 to SQL 2000, and
then SQL 2005. So are your saying this is a known bug that there currently i
s
no fix or workaround?
Thanks,
Ken
"Tracy McKibben" wrote:

> KenL wrote:
> No, this is definately a SQL 2005 bug... The article that I linked to
> talks about one possible cause of this as being a status bit in a
> converted SQL 2000 database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Autogrowth 32000 percent

I have a database with a 3G datafile, and 1G logfile. I have set the
Autogrowth on the datafile to 250M, for the third time. Somehow, I don't know
when, the Autogrowth is getting changed to [32000 percent]. So when the
datafile tries to expand it take a considerable amount of disk space (106G),
then my log dumps start failing due to low disk space.
Autogrowth=32000%
Has anyone seen this before?
Thanks in advance,KenL wrote:
> I have a database with a 3G datafile, and 1G logfile. I have set the
> Autogrowth on the datafile to 250M, for the third time. Somehow, I don't know
> when, the Autogrowth is getting changed to [32000 percent]. So when the
> datafile tries to expand it take a considerable amount of disk space (106G),
> then my log dumps start failing due to low disk space.
> Autogrowth=32000%
> Has anyone seen this before?
> Thanks in advance,
You don't say, but I'm assuming this is on SQL 2005? This is a known
bug:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy, thanks very much for the response. The feedback referenced below is
talking about SQL 2000, and says it will be fixed in the next release of SQL
Server. I am using SQL 2005, so isn't that the next release? I do not see a
resolution?
Thanks,
"Tracy McKibben" wrote:
> KenL wrote:
> > I have a database with a 3G datafile, and 1G logfile. I have set the
> > Autogrowth on the datafile to 250M, for the third time. Somehow, I don't know
> > when, the Autogrowth is getting changed to [32000 percent]. So when the
> > datafile tries to expand it take a considerable amount of disk space (106G),
> > then my log dumps start failing due to low disk space.
> >
> > Autogrowth=32000%
> > Has anyone seen this before?
> >
> > Thanks in advance,
> You don't say, but I'm assuming this is on SQL 2005? This is a known
> bug:
> http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||KenL wrote:
> Tracy, thanks very much for the response. The feedback referenced below is
> talking about SQL 2000, and says it will be fixed in the next release of SQL
> Server. I am using SQL 2005, so isn't that the next release? I do not see a
> resolution?
No, this is definately a SQL 2005 bug... The article that I linked to
talks about one possible cause of this as being a status bit in a
converted SQL 2000 database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy, again thanks for the response.
The database is in SQL 2005, and was upgraded from a SQL 7 to SQL 2000, and
then SQL 2005. So are your saying this is a known bug that there currently is
no fix or workaround?
Thanks,
Ken
"Tracy McKibben" wrote:
> KenL wrote:
> > Tracy, thanks very much for the response. The feedback referenced below is
> > talking about SQL 2000, and says it will be fixed in the next release of SQL
> > Server. I am using SQL 2005, so isn't that the next release? I do not see a
> > resolution?
> No, this is definately a SQL 2005 bug... The article that I linked to
> talks about one possible cause of this as being a status bit in a
> converted SQL 2000 database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

AutoGrowth - Which data file does SQL Prefers to auto grow & why ?

Win2k3, SQL 2000, SP4
If we have multiple datafiles in a filegroup, lets say 8,
How does SQL distribute data ?(Will all data for a single table will go in
one file or will it be spread)
When there is need for space are all files auto grown uniformely OR just one
file ?
If just one file, will the same file auto grow next time too ?
Sorry to put too many questions ? Just trying to understand the Auto grow
feature of SQL.
Advance thanks for sharing the knowledge...
RangaHi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_9sab.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2ak3.asp
Filegroups use a proportional fill strategy across all the files within each
filegroup. If there are multiple files in a filegroup, they do not autogrow
until all the files are full. The file to grow is then chosen using a
round-robin algorithm.
John
"Ranga" wrote:
> Win2k3, SQL 2000, SP4
> If we have multiple datafiles in a filegroup, lets say 8,
> How does SQL distribute data ?(Will all data for a single table will go in
> one file or will it be spread)
> When there is need for space are all files auto grown uniformely OR just one
> file ?
> If just one file, will the same file auto grow next time too ?
> Sorry to put too many questions ? Just trying to understand the Auto grow
> feature of SQL.
> Advance thanks for sharing the knowledge...
> Ranga

AutoGrowth - Which data file does SQL Prefers to auto grow & why ?

Win2k3, SQL 2000, SP4
If we have multiple datafiles in a filegroup, lets say 8,
How does SQL distribute data ?(Will all data for a single table will go in
one file or will it be spread)
When there is need for space are all files auto grown uniformely OR just one
file ?
If just one file, will the same file auto grow next time too ?
Sorry to put too many questions ? Just trying to understand the Auto grow
feature of SQL.
Advance thanks for sharing the knowledge...
Ranga
Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_9sab.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2ak3.asp
Filegroups use a proportional fill strategy across all the files within each
filegroup. If there are multiple files in a filegroup, they do not autogrow
until all the files are full. The file to grow is then chosen using a
round-robin algorithm.
John
"Ranga" wrote:

> Win2k3, SQL 2000, SP4
> If we have multiple datafiles in a filegroup, lets say 8,
> How does SQL distribute data ?(Will all data for a single table will go in
> one file or will it be spread)
> When there is need for space are all files auto grown uniformely OR just one
> file ?
> If just one file, will the same file auto grow next time too ?
> Sorry to put too many questions ? Just trying to understand the Auto grow
> feature of SQL.
> Advance thanks for sharing the knowledge...
> Ranga

AutoGrowth - Which data file does SQL Prefers to auto grow & why ?

Win2k3, SQL 2000, SP4
If we have multiple datafiles in a filegroup, lets say 8,
How does SQL distribute data ?(Will all data for a single table will go in
one file or will it be spread)
When there is need for space are all files auto grown uniformely OR just one
file ?
If just one file, will the same file auto grow next time too ?
Sorry to put too many questions ? Just trying to understand the Auto grow
feature of SQL.
Advance thanks for sharing the knowledge...
RangaHi
Check out
http://msdn.microsoft.com/library/d...r />
_9sab.asp
and
http://msdn.microsoft.com/library/d...>
_02_2ak3.asp
Filegroups use a proportional fill strategy across all the files within each
filegroup. If there are multiple files in a filegroup, they do not autogrow
until all the files are full. The file to grow is then chosen using a
round-robin algorithm.
John
"Ranga" wrote:

> Win2k3, SQL 2000, SP4
> If we have multiple datafiles in a filegroup, lets say 8,
> How does SQL distribute data ?(Will all data for a single table will go in
> one file or will it be spread)
> When there is need for space are all files auto grown uniformely OR just o
ne
> file ?
> If just one file, will the same file auto grow next time too ?
> Sorry to put too many questions ? Just trying to understand the Auto grow
> feature of SQL.
> Advance thanks for sharing the knowledge...
> Ranga

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