Trace flag 1117 is a setting that is often misunderstood.  When enabled it forces any file growth event on a data file to include all data files in the same file group so that the files can remain the same size.  I usually hear it described as a setting that changes how TempDB files are treated as they need to grow. While true, what is interesting is that this is more of a side effect of the flag.  The trace flag is not TempDB specific and affects all databases on a SQL Server instance that have more than 1 file in any file group and TempDB simply gets caught up in this functionality.

With this in mind, be sure that T1117 isn’t having unintended consequences on non-TempDB data files. Further, this functionality changed with the introduction of SQL Server 2016. Starting with 2016 the T1117 (T1118 too!) functionality is enabled for TempDB only by default and the trace flag only affects DBs other than TempDB. Is it time to update your best practice documentation?

If the server is running an older version of SQL Server, T1117 cannot be turned on, and there is still a desire to keep all TempDB files the same size this could present a problem.  This can be mitigated by simply right-sizing the TempDB data files from the start so that they don’t ever need to grow.  Even with T1117 turned on there is still a case to be made for simply right-sizing the files from the start since file growth events are blocking operations.

Leave a Reply

Your email address will not be published. Required fields are marked *

I accept that my given data and my IP address is sent to a server in the USA only for the purpose of spam prevention through the Akismet program.More information on Akismet and GDPR.

This site uses Akismet to reduce spam. Learn how your comment data is processed.