Blitz Result: Percent or 1MB Growth in Use, Slow File Growths
Growing database files by a percentage is relatively harmless when databases are small. Once you’ve grown beyond a few gigabytes, a ten percent file growth can be a cause of performance problems while SQL Server has to wait for space to be allocated.
On the other extreme, for databases over a few gigabytes, it doesn’t make sense to keep growing in tiny 1MB increments where you can run into extreme physical fragmentation.
This part of our SQL Server sp_Blitz® script looks at sys.database_files for is_percent_growth = 1, or data files over 10GB that are growing by 1MB at a time.
How To Fix the Problem
If you just have one database, you can right click on it, select “Properties” and change this manually on the “Files” tab:
- For data files, set growth to fixed units of 256MB
- For log files, set growth to fixed units of 128MB
If you have more than one database, that gets old after the first time you do it. In that case, check out our script below and customize it for your SQL Server.
How to Set File Growth for SQL Server Data and Log Files
Our standard recommendation is to grow in increments of 256 MB for data files and 128 MB for log files.
These settings modest enough in size that files won’t take long to grow, yet large enough that fragmentation from frequent growth operations is unlikely to be an issue. However, you need to consider each database separately – there’s no one size fits all solution for database file growth settings.
Why Use Autogrow At All?
For databases that are growing fast, don’t rely on autogrowth at all: proactively grow out the files so that there’s empty space each quarter. You can still leave autogrowth enabled, just in case.
Review the Files that need Changes and Get Their File Names
This script lists out all the data and log files which are currently using percent growth, and it gives you the file names. You’ll use those file names in the next script.
SELECT d.name as database_name, mf.name as file_name, mf.type_desc as file_type, mf.growth as current_percent_growth FROM sys.master_files mf (NOLOCK) JOIN sys.databases d (NOLOCK) on mf.database_id=d.database_id WHERE is_percent_growth=1 GO
Sample TSQL to Grow Your Files
Here’s a sample script that will change the file growth settings on a database:
/* Sets data file growth to 256MB increments, log file growth to 128MB increments. No max file size specified for either file. */ USE [master]; GO ALTER DATABASE SampleDB MODIFY FILE (NAME='sampledb_data', FILEGROWTH = 256MB); ALTER DATABASE SampleDB MODIFY FILE (NAME='sampledb_log', FILEGROWTH = 128MB); GO
Setting Default Growth for New Databases
In order for these to become the default settings for all new databases, you’ll need to make these changes to the model database (existing databases won’t inherit these new values).
Instant File Initialization Helps Growth of Data Files
If you followed our SQL Server Setup Checklist you should have already enabled Instant File Initialization. If not, make sure you enable Instant File Initialization – this will help for data file growth. Make sure you schedule an outage window to restart SQL Server for this setting to take effect (it requires restarting the SQL Server Service).