Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server – Check Enabled Trace Flags for Server

In SQL Server you can enable a Trace Flag at session (effective for current session only) level and global level. If you are not sure which Trace Flags have been enabled you can use DBCC TRACESTATUS() command to get a list of enabled Trace Flags.

DBCC TRACESTATUS() takes two arguments <Trace Flag Number> and <Trace Scope>.

It can be used in following different ways.

To display Trace Flags which are enabled in current session + globally enabled Trace Flags

DBCC TRACESTATUS(-1)

GO

 

DBCC TRACESTATUS()

GO

To display status of individual Trace Flags which are enabled.

DBCC TRACESTATUS(3023, 3604)

GO

The output of DBCC TRACESTATUS() is as below:

image

 

This status of Trace Flags can also be obtained from SQL Server error logs. Although it required more effort you can use xp_ReadErrorLog to get this information from error logs using below T-SQL:

EXEC xp_ReadErrorLog 0, 1, 'DBCC TRACE'

GO

You can find more information on xp_ReadErrorLog here.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe


Filed under: SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Undocumented Functions

Comments

Leave a comment on the original post [sqlandme.com, opens in a new window]

Loading comments...