Share via

What is the expected behavior from an attempt to enable a trace flag which is not defined in the targeted version of the product?

  • Article

Imagine, for example, that you run DBCC TRACEON(1117, -1) to globally enable trace flag 1117 on an instance of SQL Server 2005, where it had no effect because nowhere in the Database Engine’s code honored such flag. We could then say trace flag 1117 was in a “reserved for future use” state in SQL Server 2005.

What would happen then in such attempt? First of all, the DBCC command would run successfully to completion, it will internally go to the place where it stores an array representing the current global state of the whole range of allocated trace flag values, and will set the bit (the one corresponding to this particular flag’s value) indicating that this trace flag is enabled now. And secondly, since that version of the product doesn’t do anything with that trace flag, it will simply not alter in any way how the product behaves or responds.

The only conditions which are evaluated when a trace flag is enabled are:

1) That its ID is in the valid range. That valid range in SQL Server 2008 R2 is >= -1 AND < 9300. -1 having a special treatment, since it is not an actual traceflag ID but an indicator that other flags listed as parameters passed to DBCC TRACEON/TRACEOFF are scoped as Global instead of Session flags,

2) If it corresponds to one of the many declared flags in that range (0-9299), it will check if the flag is being enabled through the DBCC command or as a Startup trace flag (via -T command line parameter), and will make sure in the declaration of the trace flag it was marked to be enabled using that particular mechanism.

If using DBCC TRACEON you try to enable one trace flag which is declared as one which was only meant to be enabled during startup, it will report to the user error 17173 (“Ignoring trace flag %d specified during startup. It is either an invalid trace flag or a trace flag that cannot be specified during server startup.”)

If using the -T parameter you try to enable one trace flag which is intended to be used with DBCC TRACEON, it will report the same error as before, but since there is no user session to notify of the error, it is sent to ERRORLOG and Windows Event Log. Unfortunately, at the point this error is raised and reported the ERRORLOG isn’t initialized yet, so it won’t show up in there. If you happen to be running SQL Server service as a console application, the error will be seen in the console. And whether you are running SQL as a service or as a console app, it will also be written in the Application log, as an event ID 17173. If you are subscribed to XEvents event XeSqlPkg ::errorlog_written, you could also catch that error occurring because an instance of the event is produced.

If the trace flag being enabled is one not declared (as it was the case of 1117 in SQL Server 2005), it will assume it is one that can be enabled both through Startup and trough DBCC, and will not complain at all.

At first sight, there is nothing wrong with enabling one trace flag for which nowhere in the code nobody will check whether it is enabled or not to modify the product’s behavior. But, what if a future version begins using such number in a purposeful way and you simply forget you had been blindly enabling it just because it didn’t cause any harm?

For that reason, and given the fact that the majority of trace flags are not documented, only use those you are instructed to use by a CSS representative or by a member of SQL Server’s Product Group.

Avoid invoking DBCC TRACEON with random numbers without knowing for sure whether they are honored and therefore affect the default behavior or not.

Comments

  • Anonymous
    December 12, 2011
    The "-1" should be the last parameter. DBCC TRACEON(<traceflag>, -1) is the correct syntax. msdn.microsoft.com/.../ms187329.aspx

  • Anonymous
    December 18, 2011
    True it is Mike. Thanks for the warning. Although, given the way it has been implemented since 2005 at least (I haven't checked the implementation in 2000 and earlier), it accepts putting the -1 to indicate global scope, in any place. Could be the first, the second, the third, the last. Not only that, it could also be repeated many times and it would be valid and have the same effect. Therefore, running DBCC TRACEON(-1, 1117, 3605) is equivalent to running DBCC TRACONE(1117, 3605, -1) or to DBCC TRACEON(-1, 1117, -1, 3605, -1). In order to be compliant with the syntax described in the documentation, I'll go ahead and change it in my post. Thanks!