SQL Server Health Check

[Quick Guide] Autogrowth in SQL Server: Best Practices

Updated July 31, 20262 min read

SQL Server autogrowth: best practices. Ploom looks up at an overgrown plant that has cracked its small pot.

What is the autogrowth setting?

It's a procedure used by the SQL Server engine to expand the database size when all its space runs out. Files fill faster than most teams plan for, with global data creation rising from 147 zettabytes in 2024 to an estimated 181 zettabytes in 2025, per our big data statistics.

If the auto-growth setting for a database is not set correctly, the database may experience various or few auto-grow events.

When SQL Server has to grow a file, all transactions stop. They wait until file growth operation is complete to continue.

These events can introduce unpredictable hits in performance at random times (especially if disks are performing slow).

How do I configure the settings?

A good practice is to change all database file growth options by a large enough MB value instead of a percentage value or low number such as 1MB.

You can use the query below to generate a change script:

Change the growth increment [XXX] that must be large enough to avoid performance penalties.

T-SQL
SELECT 'ALTER DATABASE [' + db_name(s.database_id) + '] MODIFY FILE ( NAME = N''' + s.name + ''', FILEGROWTH = XXXMB)' AS ToExecute
FROM sys.master_files s
INNER JOIN sys.databases db ON s.database_id = db.database_id
WHERE (
		s.is_percent_growth = 1
		OR s.growth * 8.0 / 1024 < 10
		)
	AND db.state_desc = 'online'
ORDER BY s.database_id

The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depend on many factors in your environment.

Figure 1- Sample of the query output
Figure 1- Sample of the query output

Microsoft's best practices general rule for testing is to set your autogrow setting to about one-eighth the size of the file.

More information

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜