Checking and Configuring File Autogrowth for SQL Server

One thing that I seem to come across often enough is the old auto-growth settings from SQL Server. 1MB for data files, 10% for logs. This was a horrible default and is now adjusted to be a bit more reasonable, but even after adjust files I find that some still slip through the cracks.  I adjusted an old TSQL script to check for files on the server using the old settings and adjust them to use 500MB for data files and 100MB for logs.  This is just a basic script that checks and generates TSQL for the files with those defaults. It can be adjusted pretty quickly to check other things.

I hope to update this with a PowerShell script to do this at some point, but for now just wanted to create something that can be run for a server and help adjust those defaults to something that won’t cause quite as much trouble.  Of course, adjust as appropriate for your situation. These aren’t the optimal settings, but should at least be better than the old defaults.

DECLARE @NewDataFileGrowth varchar(100) = '500MB'
DECLARE @NewLogGrowth varchar(100) = '100MB'
select
CASE WHEN physical_name like '%.ldf'
THEN 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILEGROWTH = ' + @NewLogGrowth + ')'
ELSE 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILEGROWTH = ' + @NewDataFileGrowth + ')'
END as ChangeAutoGrowSettings,
DB_NAME(mf.database_id) database_name,
mf.name logical_name,
CONVERT (
DECIMAL (20, 2),
(
CONVERT(DECIMAL, size)/ 128
)
) [file_size_MB],
CASE mf.is_percent_growth WHEN 1 THEN 'Yes' ELSE 'No' END AS [is_percent_growth],
CASE mf.is_percent_growth WHEN 1
THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth / 128) + ' MB'
END AS [growth_in_increment_of],
CASE mf.is_percent_growth WHEN 1
THEN CONVERT(DECIMAL(20, 2),
( ( ( CONVERT(DECIMAL, size)* growth )/ 100 )* 8 )/ 1024 )
WHEN 0
THEN CONVERT( DECIMAL(20, 2), ( CONVERT(DECIMAL, growth)/ 128 ) )
END AS [next_auto_growth_size_MB],
CASE mf.max_size
WHEN 0 THEN 'No growth is allowed'
WHEN 1 THEN 'File will grow until the disk is full'
ELSE CONVERT(VARCHAR, mf.max_size) END AS [max_size],
physical_name
from
sys.master_files mf
where
CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth / 128) + ' MB'
END IN ('10%', '1 MB')

view raw
File_Autogrowth.sql
hosted with ❤ by GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *