Check the status of the trace in the SQL Server

SQL Server has 3 scopes of trace: Query, Session, and Global.

Check the Status of the trace is enabled or disabled:

dbcc tracestatus

Note: No row return means no trace is enabled.

Enable the trace at the Global level in the SQL Server: use the -1 option for the global level:

DBCC traceon(4199,-1)

Disable the trace in the SQL Server:

DBCC traceoff(4199,-1)

Note: DBCC TRACEON option is lost when SQL Server restarted.

Note: -T startup option to specify that the trace flag is set on during the startup command in windows to permanently enable the trace at the global level.

Enable trace at query level in SQL Server by using Hint:

SELECT columname FROM table WHERE columname = 100 OPTION (QUERYTRACEON 4199, QUERYTRACEON 4137);