Auto Growth - Best Practices

  • What are the autogrowth settings best practices ? How about Auto Shrink and Auto Update Statistics, Is it suggested to keep them TRUE? I understand Auto Shrink is to be turned off to reduce fragmentation..

    I understand that these values depend on growth of database, take an idle scenario where 50 users will be accessing regularly.

    Below are the values (DBID, Size, MaxSize, Growth). Please suggest your recommendations as well.

    52133248-1 128

    533376268435456 10

    6284576-1 12800

    62879264128 6400

    7339200-1 1280

    7158080-1 640

    7870400-1 6400

    7966400-1 6400

    82142592-1 128

    81400268435456 10

    96922976-1 10

    9128-1 30

    10806400-1 6400

    1064000-1 6400

    1111308960-1 10

    1110360-1 30

    12518400-1 6400

    1216640-1 6400

    13339200-1 1280

    13158080-1 640

    13870400-1 6400

    13966400-1 6400

    14284576-1 12800

    1428792268435456 6400

    15339200-1 1280

    15158080-1 640

    15870400-1 6400

    15966400-1 6400

  • This is a classic "It Depends" question. The usage of the databases is going to vary, in particular the rate of data growth and data changes and whether that is online or batch (eg. most reporting databases are only updated by scheduled batch ETL processes), so the way you handle the growth of them is also going to vary.

    The MaxSize of -1 means there is no limit. I'm not sure why a couple of your files (log files?) have really large limits: that seems a bit pointless, especially given their current sizes. The files with a Growth of 10 are probably set to grow in 10% increments which I tend to avoid but that's mainly personal preference.

    We find that most growth occurs during index rebuilds (because temporarily there's two copies of the index). Since they're generally done out of core hours then we tend to make our growth increments fairly large on our databases: eg. for a 50GB file we might have a growth increment of 20GB. However, if the database is being archived we might make it very small.

    The other factor is that under SQL2k5 and above there's no file initialisation to wait for (assuming your SQL Server service account has the "Perform Volume Maintenance Tasks" right), so this means that larger growth increments don't hurt as much. If you don't have that right and are initialising the files (and thus the "new bits" when they grow too) you might find a smaller growth increment is necessary to prevent overrunning your maintenance window,or even worse causing your online users to bog down while waiting for a file to be initialised if that happens during business hours.

  • I agree with Glenn. It really depends on your data growth. I advise you to monitor the rate at which your data grows on each database and sit at a final value. Do not set auto growth in 'percent' which is a bad idea, always set autogrowth in Mb/GB values.

  • Thank You Both of you for your ideas, i am going to monitor growth for one week and will decide on it..

    Thanks,

    Sudhie.

  • Regarding the growth settings, I agree It Depends. Some people advise proper capacity planning and setting zero growth, some advise setting an appropriate growth increment for the file size.

    Where you do allow growth, it is considered better to set growth to a fixed MB or GB amount rather than use a % growth, as this way you can predict how your disk space will get used.

    You are right in thinking that 'Auto Shrink' should be turned off. Likewise with 'Auto Close'. Repeated shrink and growth of a database file will cause NTFS fragmentation which will harm your performance. Repeated open and close of database files will slow down the query response for the user who causes the files to open.

    It is normaly good practice to set 'Auto Update Statistics' on, and also set 'Update Statistics Asyncronously' on. This allows SQL Server to trigger a stats update when it feels it is needed, and the asyncronous stats update means the query that triggered the stats update is not waiting for the update to complete before it returns to the user. There are some situations where you do not want the stats to be updated, but this is very rare and should only be done where you fully understand the reasons and implications of disabling stats update.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • One additional warning about setting the AutoGrowth size on your databases, especially if you're using SQL 2005, no service packs. Generally the recommendation (and it was mentioned in this thread) is to have files grow in fixed increments: 100MB, 500MB, etc. However, in SQL 2005 with no service packs, when the SQL service is restarted, it will change autogrowth properties that specifiy a fixed amount to grow, and change that increment to 12800%. The next time one of your files needs to grow, you'll probably be getting phone calls about error messages, low disk space warnings, etc. Been there, done that, got the t-shirt.

    Another reason to make sure you're keeping up with service packs.

    David

  • Instead of relying on AUTOGROW,

    consider running a script which runs weekly to provide you the size of the database and used extents/mb, along with percentage of datafile used. When the database size gets within a certain predetermined threshhold (say 90%), you then manually grow the file by a significant amount during a conveinant time (off hours).

    By taking a proactive approach, you will avoid the database "automatically" growing in the middle of the day thereby causing all kinds of performance issues.

    You can use the script technique as well as leaving "autogrow" on, just in case the dba is away for an extended period of time and cannot manually grow the db. You could always automate the entire process. When db is within xx%, then schedule the database to grow.

    I believe someone posted a script at this site that provides the database size information.

    steve

  • Wow, this is really good piece of information from all of you. Thank You.

    Now i have been monitoring growth for few days and the growth is very less, Some times negative (Are logs the reason? )

    Considering this growth, i wish to choose these settings

    Database size Below 1 GB - 100 MB Growth and Restricted to 10 GB

    1 GB to 5 GB - 500 MB Growth and Unrestricted and Restricted to 10 GB

    Above 5 GB - 1 GB Growth and Restricted to 10 GB.

    i am running DB growth script every 4 hours and hardly seeing any growth rate, Shall i leave logs also with similar settings?

    Keeping these settings, i wish to monitor them and increase the space manually as suggested by Steve.

    Thanks,

    Sudhie.

  • I like to set the autogrow to 10% with a reasonable limit. If you set autogrow to lets say 1 mb and you are inserting a 100 mb of data then SQL Server has to run a database extent 100 times. Which causes unnecessary overhead and can lead to sessions being blocked because the disk is busy taking care of the extent request.

    Your Database files are contiguous files on the drives if other data is being written to that drive SQL Server has to tell the OS that it needs to now move what ever files were written to the place on the disk that would interfere with expanding the database file. As you can see, setting the database file to the best size and never letting it autogrow is the best option.

    I consider autogrow a failsafe in case one of the databases I set up is growing due to a new batch job or software changes the DBA doesn't hear about until you get the 2AM call that the database files are full.

  • 1) monitoring for a few days is meaningless for db growth considerations.

    2) you need to figure out how big each database will be over the course of 12 to 18 months and make it that big now. use autogrowth appropriately to avoid unexpected scenarios. reevaluate every few months. there are MANY reasons for having this extra free space inside the data file.

    3) if you have negative growth then I would imagine there is a shrink happening somehow.

    4) buy a few DBA administration books and read them thoroughly. Also consider taking a class or two. You need to be much more knowledgeable than you are if you are going to be filling out the dba roll at your company. Not knowing things like the questions you asked make me think you may not know much more important things that DBAs should know such as disaster recovery situations, index maintenance, etc.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 10 posts - 1 through 9 (of 9 total)

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