SQL Server Trace Flags DBCC TRACESTATUS

Hi Friends,

This is my second blog in the series of SQL Server Trace Flags. You can go to previous blog by clicking here.

There may be a situation when you want to know which trace flag is enabled right now. There is one DBCC command and you can use that to find such type of information i.e.

DBCC TRACESTATUS (‘insert trace flag here’)

1-      If you wants to know all the enabled trace flags for current session:

DBCC TRACESTATUS ()

2-      If you wants to know all the trace flags enabled globally:

DBCC TRACESTATUS (-1)

3-       If you wants to know a particular trace flag is enabled at session level or not:

DBCC TRACESTATUS (xxxx)

4-      If you wants to know the status of multiple trace flags:

DBCC TRACESTATUS (xxxx, xxxx)

5-      If you wants to know a particular trace flag is enabled globally or not:

DBCC TRACESTATUS (xxxx, -1)

Note: you will replace xxxx with trace flag number.

Let me show you this thing by an example:

DBCC TRACEON (3604) -- to enable at session level
go
DBCC TRACEON (3605,-1) -- to enable at global level
go
DBCC TRACESTATUS () -- List out all the enabled trace flags for current session 
go
DBCC TRACESTATUS (3604) -- particular trace flag is enabled at session level or not
go
DBCC TRACESTATUS (3604, 3605) -- to know the status of multiple trace flags
go
-- do not forget to turn off both trace flags because we use these just for demo purpose
go
DBCC TRACEOFF (3604) -- to turn off at session level
go
DBCC TRACEOFF (3605,-1) -- to turn off globally
go

SQL Server Trace Flag DBCC TRACESTATUS

PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.

HAPPY LEARNING.

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

2 Comments on “SQL Server Trace Flags DBCC TRACESTATUS”

Leave a Reply

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