Hi Friends,

My today’s blog post is focused on trace flag 1117 changes in SQL Server 2016. We all know that there are multiple trace flags in SQL Server. These trace flags are generally used to change the default behavior of SQL Server. One of these trace flag is 1117. This trace flag is generally used to enable the growth for all the files under the file group. You can read the same in one of my previous blog post by clicking here.

In SQL Server 2016, you can not change that default behavior by using trace flag 1117. If you want to grow all the files under the file group at the same time, then you have to use the below command:

Let me show you that the trace flag 1117 is not working with SQL Server 2016 R0:

Trace Flag 1117 Change in SQL Server 2016

Now let me try to show you the auto grow all files in SQL Server 2016 R0 using Alter Table syntax. One of the major benefits of this syntax is: you can use the auto grow all files behavior for any specific file group and any specific database. In below TSQL example, I am going to create two file groups and I’ll also create two tables, one for each file group. I’ll enable the auto grow all files feature only for PRIMARY Filegroup:

Trace Flag 1117 Change in SQL Server 2016 Filegroup Level

From the above output, you can see that auto growth for all files happen only in that file group for which we have enabled the auto grow all file settings.

Now if you want to check the name of file groups for which ‘autogrow_all_files’ is enabled then you can use the below TSQL syntax:


PS: For tempdb database, behavior of trace flag 1117 is by default enabled. So, for tempdb in SQL Server 2016 there is no need to enable the TF 1117.



Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook