Percent Growth in Use

Blitz Result: Percent or 1MB Growth in Use, Slow File Growths

Does this filegrowth unit make my data look big?

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 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.

Return to sp_Blitz or Ask Us Questions

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

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

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

/* Sets data file growth to 256MB increments, log file growth to 128MB increments. No max file size specified for either file. */

USE [master];

ALTER DATABASE SampleDB MODIFY FILE (NAME='sampledb_data', FILEGROWTH = 256MB);

ALTER DATABASE SampleDB MODIFY FILE (NAME='sampledb_log', FILEGROWTH = 128MB);

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).