What are SQL Server Trace Flags and How to Use Trace Flags in SQL Server
Trace flags in SQL Server are provided to temporarily set specific server characteristics or to switch off a particular behavior. Trace flags are generally used to diagnose performance issues or to debug stored procedures or complex computer systems.
In SQL Server, there are two types of trace flags: session level and global level. Session trace flags are active only for a connection in which they are enabled and are visible only to that connection. Global trace flags are set at the SQL server instance level and are visible to every connection on the SQL server instance. Some flags can only be enabled as global, and some can be enabled at either global or session scope.
Trace flags are set on or off by using either of the following methods:
1. Using the DBCC TRACEON and DBCC TRACEOFF commands.
DBCC TRACEON(3004) will enable the trace flag at session level.
DBCC TRACEON with the -1 argument will enabled the trace flag at global level, example DBCC TRACEON(3004, -1)
DBCC TRACEOFF is used to turn off a particular trace flag
DBCC TRACEOFF(3004) will disable the session level trace flag.
DBCC TRACEOFF with the -1 argument will disable the trace flag at global level, example DBCC TRACEOFF(3004, -1)
DBCC TraceStatus can be used to get the information weather a particular trace flag is enabled or not.
2. Trace flag enabled using DBCC TRACEON is only active till restart of SQL Server instance. So after SQL instance is restarted, you need to enable the trace flag again. If you want to avoid enabling the traceflag after restart of SQL Server instance, then you can enabled the trace flag from StartupParameters in SQL Server configuration Manager.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.