SQL SERVER – Trace Flags

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

Below are some basic questions and its answers on this.

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?

There are many resources you can find in internet. Below are some reliable sources on trace flag.

Blog By Prince Rastogi

MSDN

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published. Required fields are marked *