Find and Modify File Growth Settings for All Databases

I have a client who is challenging me in a lot of ways.  They have over a thousand databases in one of their 2 SQL instances.  Over. A. Thousand.  Admittedly, the databases are not big but the sheer quantity of them is daunting to say the least.  To make it more fun, some of the databases have multiple files.  

To some of you out there, that many databases might not be a big deal.  To me, it is a big deal.  That many databases can cause many admin tasks to take a very long time.  One such task is to review all databases to make sure that they are not growing their files by 1MB or 10 Percent, especially the transaction log files.  I find those default settings to not be a good thing generally, and have learned to set files to grow by specified amounts if auto growth needs to occur.  I also try to manually grow data files during maintenance periods but we all know that auto growth happens.

So…how do we find out if our databases have 1MB or 10 Percent file growth set up?

-- Find all data files with Percent or 1MB growth
SELECT d.name as database_name,
	mf.name as file_name,
	mf.physical_name as file_path,
	mf.type_desc as file_type,
	CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,mf.size)/128)) as filesizeMB,
	mf.growth as growth,
	'Percent' as growth_increment
FROM sys.master_files mf 
JOIN sys.databases d  ON mf.database_id=d.database_id
WHERE is_percent_growth=1
UNION
SELECT d.name as database_name,
    mf.name as file_name,
	mf.physical_name as file_path,
    mf.type_desc as file_type,
	CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,mf.size)/128)) as filesizeMB,
    (CASE WHEN mf.growth = 128 THEN 1 END) AS growth,
	'MB' as growth_increment
FROM sys.master_files mf 
JOIN sys.databases d ON mf.database_id=d.database_id
WHERE is_percent_growth=0
AND mf.growth = 128
ORDER BY d.name, mf.name

There is your list.  Now, if you have a few databases that you want to make sure grow by something larger than 1MB or 10 Percent you can easily make changes using SSMS or with code.  But, my client has THOUSANDS of databases and files.  I would be making changes for weeks if I managed things manually.

I took some of the above code and put it in a lovely cursor that will automatically create the statements that I need to change all files.  I decided to base the query on file size instead of using a blanket statement to set file growth.  Maybe it’s just me but it seems kind of silly to have a 10MB database that grows by 500MB at a time.  I like to set an auto growth increment that seems reasonable to me based on the file size.

Here are my preferences for file growth settings….

If Database (mdf) size is:

• BETWEEN 0 AND 10 (MB), set FILEGROWTH to 10MB –> FILEGROWTH = 10
• BETWEEN 10 AND 50 (MB), set FILEGROWTH to 20MB –> FILEGROWTH = 20
• BETWEEN 50 AND 200 (MB), set FILEGROWTH to 50MB –> FILEGROWTH = 50
• BETWEEN 200 AND 1000 (MB), set FILEGROWTH to 100MB –> FILEGROWTH = 100
• BETWEEN 1000 AND 20000 (MB), set FILEGROWTH to 250MB –> FILEGROWTH = 250
• BETWEEN 20000 AND 40000 (MB), set FILEGROWTH to 500MB –> FILEGROWTH = 500
• BETWEEN 40000 AND 100000 (MB), set FILEGROWTH to 1000MB –> FILEGROWTH = 1000
• BETWEEN 100000 AND 200000 (MB), set FILEGROWTH to 2000MB –> FILEGROWTH = 2000
• BETWEEN 200000 AND 300000 (MB), set FILEGROWTH to 3000MB –> FILEGROWTH = 3000
• BETWEEN 300000 AND 400000 (MB), set FILEGROWTH to 4000MB –> FILEGROWTH = 4000

Log files are a whole ‘nother thing.  The idea for file growth for log files is to make the growth increments big enough to keep virtual log files (VLF’s) to a minimum.  It is vastly different than managing a data file (mdf).

If Tlog (ldf) size is:

• BETWEEN 0 AND 2000 (MB), set FILEGROWTH to 250MB –> FILEGROWTH = 250
• BETWEEN 2000 AND 4500 (MB), set FILEGROWTH to 500MB –> FILEGROWTH = 500
• BETWEEN 4500 AND 200000 (MB), set FILEGROWTH to 1000MB –> FILEGROWTH = 1000
• BETWEEN 200000 AND 500000 (MB), set FILEGROWTH to 1000MB –> FILEGROWTH = 2000

Here is the code I wrote that will create the ALTER DATABASE statements for each file.  There are 2 commented lines where you can tell it what file size range to look for and you can set file growth to a reasonable size in MB.  While I have the statements being printed for now, I put in an EXEC command that can be uncommented to allow the ALTER DATABASE statements to be run.

DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 

DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN ('master','model','msdb','tempdb')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN 200 AND 1000  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = 'SECONDARY') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename

WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = 'ALTER DATABASE [' + @dbname + '] MODIFY FILE (NAME = N'''+@filename+''', FILEGROWTH = 10)' --Change to correct MB preference
	 --EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END

CLOSE dbfiles
DEALLOCATE dbfiles

Here is what I get when I look for files between 200MB and 1GB and set the filegrowth to 100MB.

I did this for my thousand plus databases and had statements to change the settings in just a few seconds.  Slick!! 

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!