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:
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:
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!
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
[…] Lori Brown has a challenge: […]
[…] post Find and Modify File Growth Settings for All Databases appeared first on […]