AUTOGROW_SINGLE_FILE in SQL Server 2016: Syntax & TF 1117 Comparison

SQL Server 2016 introduces two new options to ALTER DATABASE statement which can be used to modify database files and these options are namely AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES.

Difference Between Trace Flag 1117 and AUTOGROW_SINGLE_FILE

In the earlier versions of SQL Server to achieve similar functionality you could use the trace flag 1117. This would allow all the files of the database to auto grow at the same time if they are configured files to auto grow. 

Comparison Table: Trace Flag 1117 vs. 2016 Enhancement

One of the biggest disadvantages of using trace flag 1117 is that in the event of auto growth all the files of the database will grow. Since this trace flag is set at global level, all the databases residing on the SQL Server Instance will auto grow as per the relevant auto growth settings configured for each database file. This scenario would result in filling drive space faster. 

AUTOGROW_SINGLE_FILE: This is the default option used when a database is created in SQL Server 2016. Whenever, AUTO GROWTH is triggered it will only grow the particular DATA or LOG file leaving behind rest of the files available within the file group at their respective size.

How to Enable AUTOGROW_SINGLE_FILE Syntax and Examples

ALTER DATABASE Adventureworks MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE
GO

Performance Impact: Proportional Fill vs. Storage Bloat

One of the most critical, yet often overlooked, reasons to use AUTOGROW_ALL_FILES in SQL Server 2016 is to maintain the integrity of the Proportional Fill Algorithm.

In SQL Server, when a filegroup contains multiple data files, the engine does not write data to them in a simple round-robin fashion. Instead, it uses a proportional fill strategy, where it prioritizes writing data to files with the most free space.

The “Hotspot” Problem in Legacy Versions

Before the 2016 enhancement, if you had four files in a filegroup and only one file hit its growth threshold, only that single file would grow. This created an immediate imbalance:

• One file became larger with significantly more free space.
• SQL Server’s proportional fill algorithm would then “hammer” that one larger file with almost all subsequent write operations.
• This resulted in a disk I/O hotspot, where a single physical drive or LUN performed all the work while others sat idle.

How AUTOGROW_ALL_FILES Restores Balance

By enabling AUTOGROW_ALL_FILES, you ensure that every file in the filegroup grows by the same increment at the exact same time. This keeps the free space across all files synchronized.

For high-throughput environments—especially TempDB workloads—this is a game-changer. It ensures that your I/O throughput is distributed evenly across all available underlying storage, maximizing your hardware investment and preventing the dreaded latch contention often seen when files grow unevenly. In modern SQL Server performance tuning, this is no longer a luxury; it is a best practice for eliminating storage-level bottlenecks.

Trending SQL Server Articles and Tips

AUTOGROW_SINGLE_FILE Frequently Asked Questions (FAQs)

1. What is the purpose of AUTOGROW_SINGLE_FILE in SQL Server 2016?

AUTOGROW_SINGLE_FILE is the default setting for user databases. It ensures that when a database reaches its capacity, only the specific file that requires more space will grow. This is designed to preserve disk space by preventing other files in the filegroup from expanding unnecessarily.

2. How does AUTOGROW_SINGLE_FILE differ from the legacy Trace Flag 1117?

Trace Flag 1117 forced a global “grow all files” behavior across the entire SQL Server instance. In contrast, AUTOGROW_SINGLE_FILE allows you to maintain the standard single-file growth behavior on a per-filegroup basis. If you need to replicate the old TF 1117 behavior for a specific database, you should check our guide on AUTOGROW_ALL_FILES in SQL Server 2016.

3. Why is AUTOGROW_SINGLE_FILE the default for user databases?

It is the default to avoid “storage bloat.” In many non-critical databases, growing all files simultaneously can lead to rapid disk exhaustion. However, for high-performance workloads like TempDB, this default is often changed to ensure a balanced proportional fill algorithm.

4. Can I switch from SINGLE_FILE to ALL_FILES growth easily?

Yes. You can toggle this setting using a simple ALTER DATABASE command. Switching is often recommended when you notice that one file in a filegroup is significantly larger than others, causing I/O hotspots. You can see the exact syntax for switching to ALL_FILES here.

5. Does using AUTOGROW_SINGLE_FILE cause disk fragmentation?

If the auto-growth increment is set too small (e.g., 1MB), any auto-growth setting can cause physical disk fragmentation and VLF (Virtual Log File) fragmentation. To optimize performance, always set a fixed, appropriate growth increment regardless of whether you are growing a single file or multiple files.

6. When should I avoid using the SINGLE_FILE growth option?

You should avoid it for TempDB and high-concurrency data filegroups. In these cases, growing only one file leads to “proportional fill” imbalances where one file handles more I/O than the others. For these scenarios, the AUTOGROW_ALL_FILES enhancement is the superior choice.

7. Is the SINGLE_FILE setting applicable to the Transaction Log?

Yes, but since SQL Server only writes to one transaction log file at a time (sequentially), the parallel performance benefits found in data files do not apply to the log. The AUTOGROW_SINGLE_FILE setting is perfectly sufficient for LDF files in most production environments.

SQL Server 2016 T-SQL Enhancement Series

• ALTER DATABASE SET AUTOGROW_ALL_FILES
• COMPRESS and DECOMPRESS Functions
• STRING_SPLIT and STRING_ESCAPE Functions
• FORMATMESSAGE Statement
• SERVERPROPERTY Function
• TRUNCATE TABLE WITH PARTITIONS
• DROP IF EXISTS
• ALTER TABLE WITH (ONLINE = ON | OFF)
• MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE