Enable or Disable trace in the SQL Server

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);


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.