Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Generates TSQL to update File Auto-Growth for SQL Server for files using 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')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.