Autogrowth option for SQL Server database files


It is not recommended to keep “In Percent” File Growth option for database files. If “In Percent” option is used for any database file and if the file size is very big, it may cause performance degradation during the file growth phase. It may not be a very big problem if “Instant File Initialization” is used but “Instant File Initialization” is only used for data files not log files. So it is always better to use “In Megabytes” option for File Growth.

image

It is recommended to keep 1024MB for data files and 256MB for log files. Log files are really important because when the transaction occurs, it is written to memory and log files first. Log files are very important because of point-in-time recovery and there shouldn’t be any latency on the log file.

The query below can be used to check the next auto growth size for each data and log files. And if is_percent_growth column equals to “Yes”, File Growth option can be selected as “In Megabytes”

--auto growth percentage for data and log files
select DB_NAME(files.database_id) database_name, files.name logical_name, 
CONVERT (numeric (15,2) , (convert(numeric, size) * 8192)/1048576) [file_size (MB)],
[next_auto_growth_size (MB)] = case is_percent_growth
    when 1 then CONVERT(numeric(18,2), (((convert(numeric, size)*growth)/100)*8)/1024)
    when 0 then CONVERT(numeric(18,2), (convert(numeric, growth)*8)/1024)
end,
is_read_only = case is_read_only 
    when 1 then 'Yes'
    when 0 then 'No'
end,    
is_percent_growth = case is_percent_growth 
    when 1 then 'Yes'
    when 0 then 'No'
end, 
physical_name
from sys.master_files files
where files.type in (0,1)
and files.growth != 0

Comments (11)

  1. Anonymous says:

    Hi, you stated above that "It is recommended to keep 1024KB for data files " but the image shows 1024 MB. IS KB a typo?

    Good article,

    Thanks

  2. Thanks for letting us know about the typo. I corrected it.

  3. Anonymous says:

    I already set autogrowth in my production server. and now I want to change the autogrowth  value. Is there any possibility of errors or corrupt  after changing the value? or are there any basic that need to be done before changing the value? Because i don't want to make mistake on production server.

    Thanks.

  4. It is an online operation. So you can change the value and apply it. It is just a database property change so it will not cause any database corruption.

  5. Anonymous says:

    HI,

    Does this recommendation holds true in case of data warehousing as well where data can be from 10 MB to 10 PB?

    Thanks

  6. Anonymous says:

    After changing the Auto Growth setting to "In Megabytes: 1024MB", the "is_percent_growth" column should display "No" instead of "Yes".

    On the other hand, can you also provide the screenshot of setting the Log file to "In Megabytes: 256MB" for consistency ?

  7. Anonymous says:

    Great script — perfect for reviewing a badly managed  Estate I've recently inherited. Thanks Cardy

  8. Anonymous says:

    Thanks for the info and the query. It's an eye opener to run it on different servers to see our autogrowth settings all over the place. I like the blanket settings of 1024MB for data files and 256MB for log files. Can you tell us the origin or resource of the sizes? Thanks, Darren

  9. Anonymous says:

    Hi Batuhan,

    Thanks for the article. Would you mind posting a link to the KB article that recommends these file size growths?

    Thanks,

    Colin

  10. Anonymous says:

    I would also like to know the rationale behind the recommended growth sizes of 1024MB (data) and 256MB (log). I am creating a policy to check for autogrowth (set to KB), and also wanted to include a check for growth sizes so this background info would be great to know. Thanks

  11. Anonymous says:

    Does we face any issue if we change the autogrowth value for data & log file for a database that is configured with mirroring,because i have to change it in prod.

Skip to main content