How to update DB File Growth Settings for ALL Databases at the same time

SQL Server’s default filegrowth settings grow a database in only 1MB increments. For larger databases this can lead to additional and unnecessary disk IO overheads, as SQL continually needs to first grow the DB file before it can write to the database. Best practice is to set these growth increments to a more appropriate size to ensure that you minimize these growth increments during peak periods.

For new SQL Server installations, this can easily be set by updating the growth settings on the model database (which is the template used for any newly created DBs in that environment). For existing environments however, a DBA could be faced with having to manually update a whole bunch of databases that reside in that environment already… and as DBAs are all busy and important people, we’ve hunted down this cool script which largely does this bulk update of all databases’ growth settings all at once.

Firstly, we’ll want to identify what the current file growth settings are for each of the existing DBs:

select 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

Then after updating line 54 below to an appropriate NEW growth increment value, we can then execute this script to update this across all DBs;

IF EXISTS(SELECT name FROM sys.sysobjects WHERE name = N'ConfigAutoGrowth' AND xtype='U')
DROP TABLE ConfigAutoGrowth
GO
CREATE TABLE DBO.ConfigAutoGrowth
(
iDBID INT,
sDBName SYSNAME,
vFileName VARCHAR(max),
vGrowthOption VARCHAR(12)
)
PRINT 'Table ConfigAutoGrowth Created'
GO
-- Inserting data into staging table
INSERT INTO DBO.ConfigAutoGrowth
SELECT
SD.database_id,
SD.name,
SF.name,
CASE SF.status
WHEN 1048576 THEN 'Percentage'
WHEN 0 THEN 'MB'
END AS 'GROWTH Option'
FROM SYS.SYSALTFILES SF
JOIN
SYS.DATABASES SD
ON
SD.database_id = SF.dbid
GO
-- Dynamically alters the file to set auto growth option to fixed mb
DECLARE @name VARCHAR ( max ) -- Database Name
DECLARE @dbid INT -- DBID
DECLARE @vFileName VARCHAR ( max ) -- Logical file name
DECLARE @vGrowthOption VARCHAR ( max ) -- Growth option
DECLARE @Query VARCHAR(max) -- Variable to store dynamic sql
DECLARE db_cursor CURSOR FOR
SELECT
idbid,sdbname,vfilename,vgrowthoption
FROM configautogrowth
WHERE sdbname NOT IN ( 'master' ,'msdb' ) --<<--ADD DBs TO EXCLUDE
--AND vGrowthOption = 'Percentage' or 'Mb'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Changing AutoGrowth option for database:- '+ UPPER(@name)
/******If you want to change the auto growth size from 100 to some other
value then just modify the filegrowth value in script at below location *********/
SET @Query = 'ALTER DATABASE '+ @name +'
MODIFY FILE (NAME = '+@vFileName+',FILEGROWTH = 128MB)' --<<--ADD AUTOGROWTH SIZE HERE
EXECUTE(@Query) 
FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption
END
CLOSE db_cursor -- Closing the curson
DEALLOCATE db_cursor -- deallocating the cursor
GO
-- Querying system views to see if the changes are applied
DECLARE @SQL VARCHAR(8000), @sname VARCHAR(3)
SET @SQL=' USE ?
SELECT ''?'' [Dbname]
,[name] [Filename]
,CASE is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR(5),growth)+''%''
ELSE CONVERT(VARCHAR(20),(growth/128))+'' MB''
END [Autogrow_Value]
,CASE max_size
WHEN -1 THEN CASE growth
WHEN 0 THEN CONVERT(VARCHAR(30),''Restricted'')
ELSE CONVERT(VARCHAR(30),''Unlimited'') END
ELSE CONVERT(VARCHAR(25),max_size/128)
END [Max_Size]
FROM ?.sys.database_files'
IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE name='##Fdetails')
DROP TABLE ##Fdetails
CREATE TABLE ##Fdetails
(Dbname VARCHAR(50),Filename VARCHAR(50),
Autogrow_Value VARCHAR(15),Max_Size VARCHAR(30))
INSERT INTO ##Fdetails
EXEC sp_msforeachdb @SQL
SELECT * FROM ##Fdetails ORDER BY Dbname
--Dropping the staging table
DROP TABLE ConfigAutoGrowth
GO

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