Update all DB growth parameters to defined increments

Having your databases grow by 1MB or percentage based increments can result in issues with many virtual log files (which can lead to long DB recovery times), or waits associated with SQL extending the data file before you’re able to write into it. Configuring appropriately size DB growth increments can help to address both these issues.

The below script offers a quick and simple solution for updating all your DBs to suitable growth increments, depending upon their size;

DECLARE @ConfigAutoGrowth as table
(
iDBID INT,
sDBName SYSNAME,
vFileName VARCHAR(max),
vGrowthOption VARCHAR(12),
vGrowth bigint ,
vsize bigint,
cmd varchar(max)
)

— Inserting data into staging table
INSERT INTO @ConfigAutoGrowth
SELECT
SD.database_id,
SD.name,
SF.name,
CASE is_percent_growth
WHEN 1
THEN ‘Percentage’
WHEN 0 THEN ‘MB’
END AS ‘GROWTH Option’,
case when is_percent_growth =1 then growth else growth*8/1024 end,
size *8/1024 ,
”
FROM sys.master_files SF
INNER JOIN
sys.databases SD
ON
SD.database_id = SF.database_id

–Change value increments, non-percentage growths
UPDATE @ConfigAutoGrowth
SET cmd =
CASE
WHEN vsize < 300 AND vGrowth <> 50 THEN ‘ALTER DATABASE [‘+ sDBName +’] MODIFY FILE (NAME = ”’+vFileName+”’,FILEGROWTH = 50MB)’
WHEN vsize BETWEEN 300 and 1000 AND vGrowth <> 100 THEN ‘ALTER DATABASE [‘+ sDBName +’] MODIFY FILE (NAME = ”’+vFileName+”’,FILEGROWTH = 100MB)’
WHEN vsize BETWEEN 1000 and 2000 AND vGrowth <> 200 THEN ‘ALTER DATABASE [‘+ sDBName +’] MODIFY FILE (NAME = ”’+vFileName+”’,FILEGROWTH = 200MB)’
WHEN vsize > 2000 AND vGrowth <> 400 THEN ‘ALTER DATABASE [‘+ sDBName +’] MODIFY FILE (NAME = ”’+vFileName+”’,FILEGROWTH = 400MB)’
END
WHERE 1=1
–and sdbname NOT IN ( ‘master’ ,’msdb’ )
AND vGrowthOption =’MB’

— Change percentage-growths
UPDATE @ConfigAutoGrowth
SET cmd =
CASE
WHEN vsize < 300 THEN ‘ALTER DATABASE [‘+ sDBName +’] MODIFY FILE (NAME = ”’+vFileName+”’, FILEGROWTH = 50MB)’
WHEN vsize BETWEEN 300 and 1000 THEN ‘ALTER DATABASE [‘+ sDBName +’] MODIFY FILE (NAME = ”’+vFileName+”’, FILEGROWTH = 100MB)’
WHEN vsize BETWEEN 1000 and 2000 THEN ‘ALTER DATABASE [‘+ sDBName +’] MODIFY FILE (NAME = ”’+vFileName+”’, FILEGROWTH = 200MB)’
WHEN vsize > 2000 THEN ‘ALTER DATABASE [‘+ sDBName +’] MODIFY FILE (NAME = ”’+vFileName+”’, FILEGROWTH = 400MB)’
END
WHERE 1=1
AND vGrowthOption =’Percentage’

–show ALTER-statements
SELECT cmd FROM @ConfigAutoGrowth
where cmd is not null
GO

And presto, we’re done… and back onto other busy and important DBA activities!