Autogrow All Files Feature in SSMS 18.0 Preview 4

One of the small improvements in SSMS 18.0 Preview 4 is the addition of an “Autogrow All Files” checkbox in the Database Properties dialog on Filegroups page as shown in Figure 1. This is another nice change that will hopefully make it easier for more people to enable this setting because the tooling actually makes it easier to do.

Checking that checkbox ends up generating the following command:

ALTER DATABASE [Test2019DB] MODIFY FILEGROUP [MAIN] AUTOGROW_ALL_FILES

This enables the same behavior for user databases as the old Trace Flag 1117, but controllable at the individual database level. This setting is explained in more detail in this Microsoft blog post:

SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases

If you have multiple files in a file group (and I think having at least two is a good idea), it is important to have them be the same size, so that SQL Server will spread the work more evenly between the files. If you have one large file and one small file, the small file tends to get ignored.

Figure 1: Database Properties – Filegroups Page

You can check the value of this setting for the current database with this query:

SELECT name, is_autogrow_all_files
FROM sys.filegroups;