How to CHECK if ACTIVE TRACE FLAGS on MICROSOFT SQL SERVER

Http://crashmag.net/how-to-check-for-active-trace-flags-on-microsoft-sql-server

You check for active trace flags by running the following query. They may is global or they may session based.

Checking for active trace flags

DBCC Tracestatus

The output could is something like the following.

Traceflag Status Global Session----------------------------4199      1      1      04616      1      1      0 (2 row (s) Affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If There is no active trace flags you'll only see

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Enable session based Trace flags

To enable trace flags only in your session with the following-commands.

DBCC TRACEON (trace#) DBCC TRACEOFF (trace#)

trace#
Is the number of the "Trace flag to turn" on.

Enable Trace Flags globally

DBCC TRACEON (TRACE#,-1) DBCC TRACEOFF (TRACE#,-1)

-1
Switches on the specified trace flags globally.

To enable a trace flag to persist through a restart. Alter the "Startup Parameters" in the Advanced tab for the SQL Server service in SQL Server Configuration Manager.

One example would be.

Before modification

-dc:\program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\data\master.mdf;-ec:\program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\log\errorlog;-lc:\program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\data\mastlog.ldf

After modification

-dc:\program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\data\master.mdf;-ec:\program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\log\errorlog;-lc:\program Files\Microsoft SQL Server\mssql10. mssqlserver\mssql\data\mastlog.ldf;-t4199

sqlservr [-sinstance_name] [-c] [-dmaster_path] [-f]
[-eerror_log_path] [-lmaster_log_path] [-M]
[-N] [-ttrace#] [-V] [-X] [-gnumber] [-h]

References

DBCC TRACEON (Transact-SQL)
DBCC traceoff (Transact-SQL)
DBCC tracestatus (Transact-SQL)
Trace Flags (Transact-SQL)

How to CHECK if ACTIVE TRACE FLAGS on MICROSOFT SQL SERVER