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.
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