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.

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)

SQL Options, SQL Server, SQL Server 2016, TraceFlags
Previous Post
SQL SERVER – FIX – Error – Msg 4928, Level 16, State 1. Cannot Alter Column Because it is ‘Enabled for Replication or Change Data Capture’
Next Post
Oracle to SQL SERVER Migration – Triggers Used While Migration

Related Posts

1 Comment. Leave new

  • Running version:
    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.

    Reply

Leave a Reply