Generates TSQL to update File Auto-Growth for SQL Server for files using the old defaults
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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