Checking and Configuring File Autogrowth for SQL Server
One thing that I seem to come across often enough is the old auto-growth settings from SQL Server. 1MB for data files, 10% for logs. This was a horrible default and is now adjusted to be a bit more reasonable, but even after adjust files I find that some still slip through the cracks. I adjusted an old TSQL script to check for files on the server using the old settings and adjust them to use 500MB for data files and 100MB for logs. This is just a basic script that checks and generates TSQL for the files with those defaults. It can be adjusted pretty quickly to check other things.
I hope to update this with a PowerShell script to do this at some point, but for now just wanted to create something that can be run for a server and help adjust those defaults to something that won’t cause quite as much trouble. Of course, adjust as appropriate for your situation. These aren’t the optimal settings, but should at least be better than 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') |
About Author
paschott
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others. I currently work for a Health-related non-profit based in Boise as a Database Architect.