How to Grow All Files in a File Group Equally? – TRACE FLAG 1117 – Interview Question of the Week #201

Question: How do you make all files in a filegroup grow together?

Answer: On SQL Server 2016 and later, use the filegroup option AUTOGROW_ALL_FILES. On older versions, the historical equivalent was global trace flag 1117.

I covered the newer setting in Introducing AutoGrow and Mixed_Page_Allocations Options. The change is easy to miss if you are used to entering a startup trace flag.

-- SQL Server2016 and later, inspect the current database:
SELECT name, is_autogrow_all_files FROM sys.filegroups;

-- Reviewed configuration example, not executed by this diagnostic script:
-- ALTER DATABASE AdventureWorks2025
-- MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES;

Uncomment the ALTER only after selecting the intended database and filegroup. It makes all files in that filegroup grow when autogrowth is triggered. It does not resize unequal existing files into equal sizes. Configure appropriate initial sizes, compatible fixed-size growth increments, MAXSIZE and available disk capacity separately.

Trace flag 1117 affected all databases on older releases. Historically, it was added as -T1117 in SQL Server Configuration Manager’s Database Engine Startup Parameters and required a service restart. Starting with SQL Server 2016, that flag has no effect; the filegroup option controls the behavior instead.

The original tutorial illustrated the flag using SQL Server 2017 Configuration Manager. That screenshot’s version is precisely why the instructions needed correction. Do not restart a modern instance or add 1117 merely to set filegroup growth. A filegroup option is the supported, narrower setting.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.