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:

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