SQL Trace flags

A handy way to find a solution to some SQL problem is by using the SQL logging.
To help you analyze a problem you can add additional data that should be traced, by using so called Trace flags.

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. Trace flags are frequently 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 and global. Session trace flags are active for a connection and are visible only to that connection. Global trace flags are set at the server level and are visible to every connection on the server. Some flags can only be enabled as global, and some can be enabled at either global or session scope.

Now, How can you activate these trace flags?
The answer is quite simple. you should just execute the DBCC TRACEON and DBCC TRACEOFF commands. And to make sure it is set globally, add the -1 argument.

For Example:

• DBCC TRACEON (2528) (set at session scope)
  
• DBCC TRACEON (2528, -1) (set as global scope)
  
• to turn the flag off again you use DBCC TRACEOFF (2528) or DBCC TRACEOFF (2528, -1)
  

Possible flags can be found at http://msdn.microsoft.com/en-us/library/ms188396.aspx
OR at http://www.mssqlcity.com/Articles/General/SQL2000TF.htm (here are some flag descriptions that aren't on MSDN).

To find out which trace flags are active, use the TRACESTATUS command, also possible with the -1 argument for the global scope. For Example:

• Status of all trace flags that are currently enabled globally: DBCC TRACESTATUS(-1)
  
• Status of trace flags 2528 and 3205: DBCC TRACESTATUS (2528, 3205)
  
• Display whether trace flag 3205 is enabled globally: DBCC TRACESTATUS (3205, -1)
  
• List all the trace flags that are enabled for the current session: DBCC TRACESTATUS()