Trace Flags in SQL Server
Trace flags plays an important role in database administration. These are frequently used to diagnose performance issues or to debug stored procedures. For example trace flag 1222 is to detect the deadlock and returns the resources and types of locks pertaining in a deadlock
In SQL Server, there are three types of trace flags, query, session and global. Query trace flags are only active for the specific query. Session traces flags are active only for that particular session or connection. Global trace flags are set at the server level and are visible to every connection on the server.
There are several types of trace flags available. Here I’ll take mostly example of deadlock trace flags which is the most common for the DBA use.
Requires membership in the sysadmin fixed server role.
A global trace flag must be enabled globally, otherwise the trace flag has no effect. It is recommended to enable global trace flag at start up, by using the –T command line option. This ensures the trace flag remains active after a server restart.
The trace flag should be enabled with appropriate scope whether it is global, session or query scope. A trace flag which is enabled at the session level never affects another session, and the effect of the trace flag is lost when the SPID that opened the session logs out.
How to set Trace flags on or off
Using DBCC TRACEON & DBCC TRACEOFF
E.g. DBCC TRACEON (1222, -1)
The effect of enabling a global trace flag with DBCC TRACEON is lost on server restart.
To turn off a global trace flag, use
DBCC TRACEOFF with the -1 argument
DBCC TRACEOFF (1222, -1)
-T (T stands as Trace flag) start up option
The –T startup option enables a trace flag globally. We can’t enable a session-level trace flag by using a startup option. This ensures the trace flag remains active after a server restart. This can be done via SQL Server configuration manager.
Note: Here we can set the trace flag in Startup Parameters as “-T1222”.
How to start\stop traces:
Dbcc traceon (trace no) – for current session
Dbcc traceon (traceno, -1) – to enable globally
Dbcc traceon (no1, no2, -1) – enable multiple traces globally
How to check trace status:
Dbcc tracestatus (-1) – display all globally enabled flags
Dbcc tracestatus () – display all trace flags enabled for current session
Note: Hi Friends,
Looking forward your thoughts on my blogs at least one liner email along with your name so that I can publish it with your name in coming blog. Kindly send it on my email id or using contact us here on the blog. Appreciate your time in advance.
Email Id: email@example.com