Did you know there is a new trace flag parameter in SQL Server 2005 and 2008 that must be set if the trace flag is to be used globally? If you use DBCC TRACEON to set a trace flag in SQL Server 2005 or 2008 and you need that trace flag to be global, you must use the -1 parameter. Trace flags in SQL Server 2000 that were global in nature, example would be executing DBCC TRACEON (1204) which would turn on deadlock reporting for all connections in SQL Server 2000. In order to accomplish the same thing in SQL Server 2005 or SQL Server 2008, you will need to execute DBCC TRACEON (1204, -1). Without the -1 option, the trace flag will only affect the connection you are in.
So this should lead you to ask if all trace flags can be global or if some of them are only local to the connection they are called. Well to answer your question, any trace flag that is called as a startup parameter will automatically be global trace flags, the parameter above only is useful when executing DBCC TRACEON. As for those using DBCC TRACEON – the following are the documented trace flags and whether they are global or local in nature. You are on your own for all of those undocumented trace flags listed out there.
SQL Server 2005
Documented trace flags which can be global or local
260
1211
1224
2528
3205
Documented trace flags which are only global
1204
1222
3625
4616
7806
SQL Server 2008
Documented trace flags which can be global or local
260
1211
1224
2440(New)
2528
3205
Documented trace flags which are only global
1204
1222
3625
4616
7806
This posting is just a small posting on one of the behavior changes between SQL Server 2000 and SQL Server 2005 or 2008. Keep reading this blog for other upgrade items you will need to know as you migrate from SQL Server 2000 or 7.0 to SQL Server 2005 or 2008. You are on your own if you are still on SQL Server 6.5 or earlier.
