none
determine the correct value for a data file database Autogrow

    Question

  • Hi All,

    I am seeing the below error occasionally in my sql server error log. Whenever I notice this log entry I see application timeout issues. 

    Autogrow of file '_data' in database 'DBName' was cancelled by user or timed out after 30077 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

    I assume during an autogrow critical database resources will be held by the engine and this causes timeouts/concurrency issues. Per MS if we reduce the autogrow to a little growth in % then that should fix it. I was wondering if anyone faced such issues and know how to determine the data file autogrow value for a database(on what basis should the autogrow % value be determined). In my scenario the database is 45GB and I am using SS2008R2 X64 SE. The autogrow value was set to 10% by default and I want to lower this value. I have transaction log backups running at 1:00Pm and 5:00PM everyday. Would adding another TX log backup help fix this issue??

    Please share your valuable inputs.

    Thanks a bunch.
    Monday, February 27, 2012 3:23 PM

Answers

  • yep..


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Marked as answer by SQLSPUser Tuesday, February 28, 2012 10:20 PM
    Tuesday, February 28, 2012 10:09 PM

All replies

  • hi

    As far as I know there is no hard written best value recommendation for this. What I would recommend is, get  your database growth for past few months. See the link bellow for the script

    http://vyaskn.tripod.com/track_sql_database_file_growth.htm

    and derive an average value from there.  Another important thing to keep in mind when setting auto growth never use percentage, specify the growth in MB.  Please also see this link as well

    http://support.microsoft.com/kb/315512/ 

    http://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/ 

    http://www.sql-server-citation.com/2010/01/common-mistakes-in-sql-server-part-5.html

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker



    Monday, February 27, 2012 3:54 PM
  • To add to what v.vt has given you, due to the size of your database, you should look at changing from the % to MB as indicated. Have a look at this article by Brad McGhee. You will be better off pre-sizing your database data files. This will give you more control, less unexpected performance hits due to the growth cycle, reduced fragmentation , which can impact your performance.

    You will want to look at utilising the instant file initialisation.

    http://msdn.microsoft.com/en-us/library/ms175935.aspx

    http://sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-instant-file-initialization.aspx

    http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server/

    as some further reading for you.

    I hope this helps


    Warwick Rudd
    MCT
    My SQL Server Blog
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------

    Monday, February 27, 2012 7:58 PM
  • Thanks for the reply guys.

    V.VT:

    per the script that you provided through vikas's url, I have noticed that the database is growing to 10% most of the times (as it is the default) so this isnt helping me with enough information required to increase the size of my current datafile. The file size in MB and Growth percentage is pasted below:

    File Size (MB) Growth Percentage (%)
    23461.69 NULL
    25807.88 10.00
    28388.69 10.00
    31227.56 10.00
    31900.44 2.15
    35090.50 10.00
    37200.56 6.01
    40920.63 10.00
    45012.69 10.00 

    So if I suddenly change the autogrowth to say 1024MB(instead of %) for this database data file would it cause any degradation in performance? Also would it fix the timeout issues during the auto growth?

    Thanks for all your help.


    Tuesday, February 28, 2012 5:16 PM
  • Warwick,

    Thanks for the url from Brad. Seriously, I didnt knew about the instant file initialization until I saw that URL. But per my post I was looking for a threshold value for the autogrowth setting which is currently set to 10% by default on a couple of databases that are 40GB and 150GB. So whats happening is during the autogrowth I see a bunch of connectivity issues like application timeouts and then the following messages logged in my sql server error log.

    Autogrow of file 'DBName_Data' in database 'DBName' was cancelled by user or timed out after 30077 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

    Autogrow of file 'DBName_Data' in database 'DBName' was cancelled by user or timed out after 30046 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

    Autogrow of file 'DBName_Data' in database 'DBName' was cancelled by user or timed out after 30061 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

    Autogrow of file 'DBName_Data' in database 'DBName' was cancelled by user or timed out after 29749 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

    Per some of the forums from MS, reducing the autogrowth to a lower value would fix this issue. But I am not sure to what value should I set this autogrowth to.

    Thanks again


    Tuesday, February 28, 2012 5:29 PM
  • Hi,

    You have a few options:

     - Out of hours, manually grow the file to what you project your database size to be in x months.

     - As per Warwick's suggestion, use Instant File Initialisation.  This will almost completeley erradicate the time taken to extend the datafile(s) (not for the transaction log)

    - Lastly, if you can't use Instant File Initialisation for whatever reason, and you don't want to manually grow the file(s) yourself, set smaller growth increments on your datafile.  However, data file growth will obviously occur more frequently, which has its own drawbacks



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you


    Tuesday, February 28, 2012 5:32 PM
  • 1. Make sure you have Instant File Initialization!!!

    2. Try to pre-allocate storage instead of having autogrow happening. Autogrow sould be considered as a last resort.

    3. A suitable autogrow size would be somewhere between 100 MB (IFI off) and 500 MB (IFI on).


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, February 28, 2012 6:20 PM
    Moderator
  • Yeps..Instant File Initialization is definitely on the schedule right now...but since my db's are 40GB and 150GB right now...I will monitor the size of the databases for this entire week and based on that size I will set the autogrow value (which I assume will be around 1 or 2 GB).

    Thanks for all your help guys.

    Tuesday, February 28, 2012 7:15 PM
  • one last thing is on my test environment I have the sqlserver services running under local system account (unlike our production env where I have the services running under a domain service account). I guess I dont need to worry about IFI for the test environment as it should already be part of "perform volume maintenance tasks"  policy because the services are running under local system. Agree?
    Tuesday, February 28, 2012 7:46 PM
  • yep..


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Marked as answer by SQLSPUser Tuesday, February 28, 2012 10:20 PM
    Tuesday, February 28, 2012 10:09 PM
  • Cool...Thanks Andrew.
    Tuesday, February 28, 2012 10:20 PM