Corrections on Trace Flag Blog Entry

Kalen Delaney and I were chatting via email about a past blog entry I'd written about trace flags. 

http://www.sqlmag.com/Article/ArticleID/92774/Trace_Flag_Changes_in_SQL_Server_2005.html

The blog entry was based on some discussions I'd listened in on between a few other SQL Server MVPs. 

In it, I said:

Oh, and there is one more change for trace flags in SQL Server 2005.  They can be specified as either local (new in SQL Server 2005) or global (as in SQL Server 2000) using an additional argument in dbcc traceon(...). If the second argument is not specified, the default value is local in SQL Server 2005, but is always global in SQL Server 2000.

Kalen found a couple of problems with the paragraph which I'd like to share with you now.

First of all, the parameter to make traceflags global was available in SQL Server 2000. Also, the default was NOT to make all traceflags global. It just frequently seemed like they were immediately set to global. But they weren’t guaranteed to be global unless you used the -1 second parameter.

If you turned on a traceflag for one session, without the global flag it would be available to all other sessions that had any traceflags of their own turned on, or if any traceflags had been set to explicitly be global.  However, a session that had no flags set would not see any flags set as local in other connections. It was basically that each session either had traceflags enabled or it didn’t.  Once a single traceflag was enabled for a session, then ALL flags enabled for ANY session would be available to that session.  

Kalen actually wrote about it here, 5 years ago:

http://www.sqlmag.com/Article/ArticleID/26406/Investigating_Trace_Flags.html

I agree with Kalen that it IS very confusing.  

I hope this helps set the record straight.

Cheers,

-Kevin