SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Autogrow not working


Database Autogrow not working

Author
Message
NANDAKUMAR-385634
NANDAKUMAR-385634
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 13

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


Pankaj Khanna
Pankaj Khanna
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 38
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
NANDAKUMAR-385634
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 13

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
Sugesh Kumar
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23673 Visits: 358
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
SDM
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 401

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
NANDAKUMAR-385634
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 13

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
Mark Harr
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7617 Visits: 202

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
NANDAKUMAR-385634
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 13

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
SQLBill
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38338 Visits: 1093
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
gopal.mailme
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 7
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search