SQL SERVER 2016 – Trace Flag 1117 is Discontinued. Use the Options Provided with ALTER DATABASE

While doing performance tuning for one of my clients, I was looking at ERRORLOG files to understand about their system. I saw that trace flag 1117 and 1118 are enabled via startup parameter.

2016-12-05 06:02:14.440 Server Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64)
2016-12-05 06:02:14.440 Server Registry startup parameters:
-T 1117
-T 1118
-d C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG

If you have not heard, then all I would say is that these trace flags are for TempDB optimization. You can read more about them in below articles.

• SQL Server 2016 – Enhancements with TempDB
• SQL Server 2016 – Introducing AutoGrow and Mixed_Page_Allocations Options – TraceFlags

While looking further, I also saw below messages in ERRORLOG

2016-12-05 06:02:14.440 Server Trace flag 1117 is discontinued. Use the options provided with ALTER DATABASE.
2016-12-05 06:02:14.440 Server Trace flag 1118 is discontinued. Use the options provided with ALTER DATABASE.

This was a good piece of advice which was given by the SQL Server engine because these trace flags are not having any use and we need to control them via database options ALTER command.

Did you know about this message in ERRORLOG? Well, I think you must check your SQL Server 2016 to see if you are using this discontinued traceflag or not? If you are using this traceflags, you can disable them in SQL Server 2016.

Reference: Pinal Dave (https://blog.sqlauthority.com)