SQL SERVER – Trace Flags – DBCC TRACEON

Trace flags are valuable tools as they allow DBA to enable or disable a database function temporarily. Once a trace flag is turned on, it remains on until either manually turned off or SQL Server restarted. Only users in the sysadmin fixed server role can turn on trace flags.

If you want to enable/disable Detailed Deadlock Information (1204), use Query Analyzer and DBCC TRACEON to turn it on.
Trace flag 1204 sends detailed information about the deadlock to the error log. It works only at server level, so for deadlocks turn it on with the -1 option shown below.

Enable Trace at current connection level:
DBCC TRACEON(1204)

Disable Trace:
DBCC TRACEOFF(1204)

Enable Multiple Trace at same time separating each trace with a comma.
DBCC TRACEON(1204,2528)

Disable Multiple Trace at same time separating each trace with a comma.
DBCC TRACEOFF(1204,2528)

To set the trace using the DBCC TRACEON command at a server level, Pass second argument to the function as -1.
DBCC TRACEON (1204, -1)

To enumerate a complete list of traces that are on run following command in query analyzer.
DBCC TRACESTATUS(-1)

SQL Server 2005 Trace Flags.

SQL Server 2000 SP3 Trace Flags. (Undocumented trace flags are not included in document)

Using Trace Flags Safely on a Real Server

A flag turned on with DBCC TRACEON does not survive a restart. If you need it to stay on, add it as a startup parameter, such as -T1222, in SQL Server Configuration Manager. Then it is active from the moment the service starts.

For deadlocks on SQL Server 2005 and later, many DBAs prefer trace flag 1222. It writes the deadlock details to the error log in a format that is easier to read than the older output. Newer versions also capture deadlocks in the built in system_health Extended Events session, so you may not need a flag at all.

A few habits I follow:

• Try a flag on a test server first, and read what it does before you turn it on.
• Keep a short note of every flag you enable, when, and why.
• Run DBCC TRACESTATUS(-1) after a restart or a patch to confirm what is really on.
• Stay away from undocumented flags unless Microsoft support asks you to use one.
• Check the scope of a flag. Some work only when turned on globally, which means passing -1 as the second argument.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.