Trace flags give you advanced mechanisms to tap into hidden SQL Server features and troubleshooting tactics. In some cases, they enable you to override the recommended behavior of SQL Server to turn on features such as network-drive support for database files. In other cases, you can use trace flags to turn on additional monitoring. There is a set of flags that help you diagnose deadlocks, including trace flag 1204.
Turning trace flags on and off
To turn on a trace flag, use the DBCC TRACEON command, followed by the trace you’d like to turn on, as shown here:
DBCC TRACEON (1204)
To turn off the trace, use the DBCC TRACEOFF command. This command is followed by which traces you’d like to turn off (multiple traces can be separated by commas), as shown here:
DBCC TRACEOFF (1204, 3625)
NOTE
Trace flag 3625 used in the previous code snippet limits the amount of information returned to users who are not members of the sysadmin server role by masking the parameters of some error messages. This can be enabled as a security measure.
When you turn on a trace, you are turning it on for a single connection by default. For example, if you turn on trace flag 1224, which disables lock escalation based on the number of locks, lock escalation is disabled only in the scope of the connection that issued the DBCC TRACEON command. You can also turn on the trace at a server level by issuing the command followed by the -1 switch, as in the following:
DBCC TRACEON (1224, -1)
Determine if the trace is running
After you turn on the traces, you’re probably going to want to determine whether the trace is actually running. To do this, you can issue the DBCC TRACESTATUS command. One method to issue the command is to interrogate whether a given trace is running, like so:
DBCC TRACESTATUS (3635)
This command would return the following results if the trace is not turned on:
TraceFlag Status Global Session --------- ------ ------ ------- 3625 0 0 0
(1 row(s) affected)
If you want to see all traces that apply to the connection, run the following command with the -1 parameter:
DBCC TRACESTATUS (-1)
As shown in the following results of this query, two traces are turned on. Trace flag 1224 is turned on globally for every connection into the SQL Server, and trace flag 3625 is turned on for this session:
TraceFlag Status Global Session --------- ------ ------ ------- 1224 1 1 0 3625 1 0 1 (2 row(s) affected)
If no traces are turned on, you would receive only the following message:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Don’t leave trace flags on for extended periods of time
Your instance of SQL Server should not have trace flags turned on indefinitely, unless you have been instructed by Microsoft Product Support to do so. When left to run all the time, trace flags may cause your instance to behave abnormally. Moreover, the flag you use today may not be available in a future release or service pack of SQL Server. If you are in debug mode, you can turn on a trace flag from the command prompt when starting SQL Server. You can also start a trace when SQL Server starts at the command prompt by calling the sqlservr.exe program and passing the –T switch after it.
There is a lot to say about trace flags even though only a few are mentioned here, but as you proceed through this book, you see a number of other trace flags in practice.
NOTE
Some functionality provided by trace flags, such as deadlock monitoring, can be more efficiently implemented by using Extended Events.
Leave a Reply