Blitz Result: Percent or 1MB Growth in Use

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.

To Fix the Problem

You need to consider each database separately – there’s no one size fits all solution for database file growth settings. 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.

For database log files, it’s best to observe log file growth patterns and determine the appropriate size for a log file. During a period of low activity, or an outage window, grow the log file to the appropriate size in 4000MB to 8000MB chunks. To determine an effective log file growth size take a look at step 7 of 8 Steps to Better Transaction Log Throughput.