Database Autogrow not working

Question

Post reply

Database Autogrow not working

NANDAKUMAR-385634

SSC-Addicted

Points: 443

November 28, 2006 at 2:10 am

#61728

Hi,

I'm having a SQLserver 2000 with 6GB diskspace left and a database which is having a7GB database giving the below error " Autogrow of file 'Database_Data' in database 'Databasename' cancelled or timed out after 30562 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size. ..."

eventhough my DB's Autogrow property value is ON. There is no space left on the datafile, where as log file is having sufficient space.

Can any one put some light into this error.

Thanks

Nanda

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Pankaj Khanna Say Hey Kid Points: 707 More actions · Answer 1

In the autogrow option, what is the growth value that you have selected for the database?

Pankaj Khanna
Database Administrator - SQL Server 2000

Keep hope to keep you intact...

NANDAKUMAR-385634 SSC-Addicted Points: 443 More actions · Answer 2

Hi Pankaj,

It is 10%. and I have 6 GB free space which is sufficient for 10% increase to my 7GB DB.!

Thanks

Nanda

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

whats the amount of RAM in your server. Also check if your server has query timed out option enabled. check SET LOCK_TIMEOUT parameter also.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

SDM SSCommitted Points: 1581 More actions · Answer 4

I'd strongly suspect query timeout (meaning the update / insert / delete statement which caused the database growth in the first place).

Looking at the error i'd bet that your query timeout is 30 seconds? growing by 10% means the server has to do perform the query, allocate and format an additional 600 MB of database and complete the transaction all within the timeout period. If your disk is fragmented this could easily exceed that.

BTW, I strongly advise leaving the auto-grow at a (moderately high) fixed percentage for exactly this reason. Autogrow is fine to get you out of a fix when you've run out of space, but is a poor substitute for careful monitoring and active capacity planning. Rather set smaller values (say, 100 MB) as the "emergency autogrow", and give yourself more room (an extra 20% or so) in planned periods when you're not trying to do maintenance and transactions at the same time.

NANDAKUMAR-385634 SSC-Addicted Points: 443 More actions · Answer 5

Hi,

Thanks for taking your time and providing some inputs. Let me put some more details regarding this.

I have 2 gb RAM on the server, query timed out option is enabled and set a value of 600 seconds.

Interestly there was no change in the settings for some time on this server and the autogrow was working too. But I faced this issue when the space on this server nearing to its maximum capacity. As I said earlier my DB size is 7GB and space left was around 6gb. does this have any relation to autogrow?!

Just to share, i could able to increase the space by 1 GB manually using the alter command.

Thanks

Nanda

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 6

You may want to check to see if there are disk quotas defined on the server. In one instance at a client's server where autogrow failed, network admins had just implemented server disk quotas, including the domain account that SQL Server was running under.

If this is the case, it is easy enough for network admin to remove quotas for specific accounts, once someone thinks of it.

Hope this helps.

Mark

NANDAKUMAR-385634 SSC-Addicted Points: 443 More actions · Answer 7

Hi,

I thought I overlooked that. but No we do not have any Quota limit defined on this server. But I think, if quota is defined I can not alter the database size mannualy, right? As I mentioned earlier, I could able to alter the DB manually, but autogrow was not working!

Thanks

Nanda

SQLBill SSC Guru Points: 51440 More actions · Answer 8

One other thing to check....in Enterprise Manager, where you set the autogrow you can also restrict growth to a specific amount. Do you have that set? If you set that, SQL Server cannot auto grow, but it will allow you to manually grow the file size.

-SQLBill

gopal.mailme Newbie Points: 7 More actions · Answer 9

Hi,,

i also have received this error on my database...

but at the same time if found that data from some of the tables has truncted.. in few tables whole data is truncated and for few many rows are truncated. few are untouched.. all the affected table are in which bulk upload was done....

kindly help, does autogrow cancelation also result in this...

Laurent Borghart SSC Enthusiast Points: 130 More actions · Answer 10

This can occur when the disks are too slow to allocate the needed space.

It's a problem with the I/O . The SQL Server wants to have more space but the response time from the disk to allocate the space gives a timeout.

You can add a data file in your files group to solve this problem

SanjayAttray SSChampion Points: 13157 More actions · Answer 11

gopal.mailme (1/15/2008)
Hi,,
in few tables whole data is truncated and for few many rows are truncated. few are untouched.. all the affected table are in which bulk upload was done....
kindly help, does autogrow cancelation also result in this...

May be you want to say - for many rows are deleted , not truncated.

Even we had same issue, and what we zeroed in was that the SAN is too old and the response time from IO is timed out when SQL Server need to auto grow its size to 10% (which is around 3 GB in our case). We moved the Databases to new server and never got this issue.

SQL DBA.

bwillyerd Right there with Babe Points: 730 More actions · Answer 12

Hello,

I had a problem with one database that does not allocate space although the autogrowth is set. The db is 65 GB and there is 98 GB of unallocated space on the array. There are no restrictions set either on the database, folder, or drive.

Just yesterday I had another database on another server that started having the same symptoms, this is a much smaller database of about 4500 MB. with 148 GB of unallocated space on the array.

I ran a SHRINKDATABASE on Wednesday after some DBREINDEX's made the Log somewhat large.

After that shrink the AutoGrowth has not performed.

This is becoming a major production issue.

Has anybody ever had these types of issues? If so, what is the remedy?

Thank you,

Bill

Laurent Borghart SSC Enthusiast Points: 130 More actions · Answer 13

hello,

Have you tried to change the settings of your "automatically grow" with " in megabytes" ?

you can try with 100 MB for example.

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 14

yes, these problems occurs when IO is too slow to allocate new space. there is a good tip to set a smaller Auto-Grow indicator;

OR

a much better solution is to:

1) resize database file manually

2) and then change autogrowth settings

databases shouldn't growth automatically - specially on sql 2000!