Trace Flags in SQL Server

Trace Flags in SQL Server

Traces

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.

Permissions

Requires membership in the sysadmin fixed server role.

Rules

1.

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

1.

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)

2.

-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

Dbcc tracestatus (-1) – display all globally enabled flags

Dbcc tracestatus () – display all trace flags enabled for current session

signature_1

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: mirza_dba@outlook.com

About Mirza Husain

Mirza Husain is a senior consultant-database and having more than 10+ years of experience in the IT industry with different domains. He is fond of writing and speaking about SQL Server and also keen to learn new technologies. He is holding MCA degree and having Microsoft certifications as MCP & MCTS. He is also ITIL certified. In his past years, he worked with many clients as Microsoft, Bank of America etc. Currently he is associated with a France based client and offering his best services. You may reach him on his email id -- mirza_dba@outlook.com. Thanks!
This entry was posted in Database Administration, Q & A, Sql Server and tagged , , , , , , , , , , , . Bookmark the permalink.

1 Response to Trace Flags in SQL Server

  1. Pingback: Top Free SQL Server Tools & Performance Tuning options from Microsoft | Mirza Husain

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s