SQL Server autogrow timeout

  • sporoy

    Hall of Fame

    Points: 3114

    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.

  • Jack Corbett

    SSC Guru

    Points: 184380

    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

    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: 184380

    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: 26672

    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

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • DNA_DBA

    SSCertifiable

    Points: 7818

    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: 134265

    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


    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[/url] :alien: but most of the time this is me :hehe:

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

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