SQL Trace

Trace flags are valuable DBA tools that allow you to enable or disable a database function temporarily. Once you turn on a trace flag, it remains on until you either manually turn it off or restart SQL Server. Some trace flags, like deadlock detection, should begin on startup.

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server 2005 error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.

You must enable the trace as 'global' using '-1', eg:

DBCC TRACEON (1204, -1)
DBCC TRACEOFF (1204, -1)

Without the '-1' the trace is a session trace
Use the DBCC TRACESTATUS command to determine which trace flags are currently active.

Use the -T command-line startup option of Sqlservr.exe. This is a recommended best practice because it makes sure that all statements will run with the trace flag enabled. These include commands in startup scripts eg:

-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T1204;-T3226

NB SQL Server must be restarted for the changes to take effect

Optimizing SQL Trace in the Books Online lists some SQL Trace performance guidelines:

• Run Profiler remotely instead of directly on server
• Avoid including events that occur frequently (e.g. Lock:Acquired) unless absolutely needed
• Include only event classes needed
• Specify limiting filters to reduce the number of events
• Avoid redundant data (e.g. SQL:BatchStarting and SQL:BatchCompleted)
• Avoid running large traces with Profiler; consider a server-side SQL Trace instead
• Limit server-side trace file size and manage space usage
• Run traces for shorter periods of time. You can control the length of time that a trace runs by enabling a stop time. This is especially important if you cannot limit the event classes or filter an event. Enabling a stop time ensures that the performance incurred does not last indefinitely.

The following may be useful:

• sp_trace_create
• sp_trace_setevent
• sp_trace_setfilter
• sp_trace_setstatus
• fn_trace_geteventinfo
• fn_trace_getfilterinfo
• fn_trace_getinfo
• fn_trace_gettable