通过

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

  • Anonymous
    March 12, 2013
    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
  • Anonymous
    March 13, 2013
    Thanks for letting us know about the typo. I corrected it.
  • Anonymous
    May 23, 2013
    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.
  • Anonymous
    May 25, 2013
    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.
  • Anonymous
    August 05, 2013
    HI,Does this recommendation holds true in case of data warehousing as well where data can be from 10 MB to 10 PB?Thanks
  • Anonymous
    September 05, 2013
    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 ?
  • Anonymous
    November 11, 2013
    Great script -- perfect for reviewing a badly managed  Estate I've recently inherited. Thanks Cardy
  • Anonymous
    January 07, 2014
    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
  • Anonymous
    April 13, 2014
    Hi Batuhan,Thanks for the article. Would you mind posting a link to the KB article that recommends these file size growths?Thanks,Colin
  • Anonymous
    July 21, 2014
    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
  • Anonymous
    December 23, 2014
    The comment has been removed