SQL Server autogrow timeout

Question

SQL Server autogrow timeout

sporoy

Hall of Fame

Points: 3114
More actions
May 18, 2009 at 2:52 am

#209528

Hi all,
I am getting the below error (SQL Server 2008 cluster) for diverse times for last three days, after giving this error in sql server logs, the db has autogrowed 2 times successfully at last, in 61 and 144 seconds, perspectively, what i am curious about is having these errors all of which has timed out in <60 seconds?
What is the default timeout for autogrow, can i set it? The data file autogrowed successfully in 144 sec, while <60 sec attempts has timed out, isn't that odd?
Thanks,
Serter Poroy
------ERROR TEXT------
Autogrow of file 'XXX' in database 'XXX' was cancelled by user or timed out after 25350 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I don't know the reason for your timeout issue, but I can say that you should reset the size of the file so that it does not need to autogrow so often. If your autogrow increment is like 10MB and it is growing twice a week then you should resize your file to take into account that type of growth.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sporoy Hall of Fame Points: 3114 More actions · Answer 2

thanks for the reply Jack, nope my autogrow is 10%, my question what could be the reason for failing of autogrow as it says failed in 5000 ms (could not be timeout a very short time)

Serter

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

As I said, I don't know the reason for the timeout, but best practices are to do the best you can to size your database files so that they do not have to autogrow, as growth is a very expensive operation and slows down your server. So if you are getting autogrow timeouts your database could become unavailable due to not enough space. The best way, in my opinion, to fix your problem, is to resize the database file(s) so that you are prepared for the growth you are experiencing.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Make sure you have instant file initialization enable:

http://www.sqlskills.com/blogs/paul/2008/08/11/HowToTellIfYouHaveInstantInitializationEnabled.aspx

Then create a method of monitoring your database file available space so that you get an alert or email to let you know that the space is running low before it runs out.

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=FileSpaceMon

DNA_DBA SSCrazy Eights Points: 9132 More actions · Answer 5

I've seen this error when the current datafile size is very large, the autogrow is set to be % rather than Mb and the files are on SAN. As the datafile gets larger the 10% or whatever the increment is becomes bigger too and SQL times out trying to create the extra space. Basically the configuration of the disks and the increment size of the file is too big to be done in a timely manner so it times out. I'd go for a Mb increase or size the file initially if you can.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 6

DNA is right on top of your issue !

Golden rule is:

- pre-size your files as accurate as you can (prefer having to much allocated !)

- if autogrowth is enabled , have autogrowth set to MB (reason: read reply DNA did)

Remark:

The autogrow will just give a timeout to sqlserver, but it will be completed if the needed space can be allocated.

As also mentioned in an earlier reply, "instant file initialization" may help out to avoid this issue.

Read about it, so you understand the consequences (DRP,..)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me