SQLcommitted

SQL SERVER – Trace Flags

To know about a topic we usually ask questions. Here I used the same method to explain about trace flag of SQL Server.

Below are some questions related to trace flags.

What is the use of Trace Flag?

Trace Flags are used to enable or disable certain functionality of SQL server temporarily. It is used to diagnose performance issues or to debug stored procedures.

Where will I get all the trace flags available for SQL Server? 

For SQL Server 2012 : – http://msdn.microsoft.com/en-us/library/ms188396.aspx

For SQL Server 2008 R2 : – http://msdn.microsoft.com/en-us/library/ms188396(v=sql.105).aspx

For SQL Server 2008 : – http://msdn.microsoft.com/en-us/library/ms188396(v=sql.100).aspx

For SQL Server 2005 : – http://msdn.microsoft.com/en-us/library/ms188396(v=sql.90).aspx

What are the types of Trace Flags?

SQL Server provides below two types of trace flag.

Session Level: Active and visible only to a specific connection where it is set. A session level trace flag never effect any other session.

Global Level: Active and visible at the server level (for all connections). Some trace flag can only be set at global level. If a global trace flag enable at session level there will be no effect of the flag on the server.

Note: Some of the trace flags can be enabled either at session level or at global level.

How to enable or disable a Trace Flag?

Using method DBCC TRACEON, we can enable a trace flag.

Ex: DBCC TRACEON (1224)

To set a trace flag at Global level use argument -1.

Ex: DBCC TRACEON (1224, -1)

Global level trace flag can also be set using –T start-up option. Using –T start up option we can’t enable any session level trace flag.

How to check a trace flag is enabled or disabled?

Using method DBCC TRACESTATUS, we can check the status of a trace flag.

Ex: DBCC TRACESTATUS (-1) will display list of trace flags enabled at global level

DBCC TRACESTATUS () will display list of trace flags enabled for current session

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql.

December 17, 2012 - Posted by SQLcommitted | Transact-SQL | DBCC, SQL Server, SQLcommitted, T-SQL, Trace Flags, Transact-SQL