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