Trace Flags

Trace Flags

SQL Trace Flag?

Trace flags are used to set temporary behavior or characteristics or to switch on or off particular behavior by DB engine. In particular when diagnosing the cause of error by some queries inside the DB engine.

Ways to do it?

*** user needs “sysadmin” fixed server role to enable or disable the traces.

There are 3 well know ways to enable “Trace”.

  1. Using DBCC TRACEON (XXX, XXXX, XXXX)
  2. Adding the trace flag at the startup Parameter
  3. Through Registry / setting the directory of SQL server from BINN folder.

To DISABLE trace DBCC TRACEOFF.

TO check the status of trace flags Globally DBCC TRACESTATUS.

Explaining each one below.

DBCC TRACEON (TRACE#1, TRACE#2, ……) [-1] [with NO_INFOMSGS];

GO

Above syntax explains two ways of using the same DBCC to enable trace, locally only limited for current session once closed the session trace is not enabled any more, and by using -1 with the DBCC command it will enable through globally (server level and are visible to every connection on the server).

As a STARTUP PARAMETER

To do this we need to stop and start the ending using configuration manager. And *this needs to separated by “;-TXXXX “ semi-colon used to separate the trace flag from before parameters used to start up the engine this is mandatory.

Properties àAdvancedàStart Parametersà -d— followed by the binn path where you have placed the master mdf, Errorlog and master log file locations followed by terminated by “;” and then trace flag.

After setting this need to restart the service and then the trace will take effect globally.

Enable Trace at the registry (undocumented)

This is will enable trace permanently equal effect as the startup parameter.

Need to restart after edited the registry parameter.

Using “regedit” at run open up the registry tree , and dig down to “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.XXXX\MSSQLServer\Parameters” where XXXX is the instance name , for default it is MSSSQLSERVER , Now to add the trace flag right click on the Parameters folder and click on New and then String Value

Provide the name as SQLArgN (where N is the incremental value from previous string value, in this case its 3) and then right click on the key value to modify the value.

Now provide the value as -TXXXX (where XXXX is trace number).

Tracking the trace flags use

DBCC TRACESTATUS(-1).

***NOTE never use –t how ever this is accepted by sql server engine it will also enables few of the internal trace flags which might collapse if not handled properly, primarily used by engineers to diagnose the engine issues.

Links useful for supporting and reading,

https://msdn.microsoft.com/en-us/library/ms188396.aspx

Advertisements

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 )

Google+ photo

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

Connecting to %s