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:
-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)
Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) – 13.0.5337.0 (X64)
May 16 2019 02:24:21
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)
I am seeing this Message and not the you mentioned in the article.
DBCC TRACEON 1117, server process ID (SPID) 40. This is an informational message only; no user action is required.