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

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