Database Reference
In-Depth Information
Figure 21-2.
A deadlock graph as displayed in the Profiler
I'll show you how to use this in the “Analyzing the Deadlock” section later in this chapter.
The two trace flags that generate deadlock information can be used individually or together to generate different
sets of information. Usually people will prefer to run one or the other because they write a lot of information into
the error log of SQL Server. The trace flags write the information gathered into the log file on the server where the
deadlock event occurred. Trace flag 1222 provides the most detailed information on the deadlock.
Trace flag 1204 provides detailed deadlock information that helps you analyze the cause of a deadlock. It sorts
the information by each of the nodes involved in the deadlock. Trace flag 1222 also provides detailed deadlock
information, but it breaks the information down differently. Trace flag 1222 sorts the information by resource and
processes, and it provides even more information. Both sets of data will be discussed in the “Analyzing the Deadlock”
section.
The
DBCC TRACEON
statement is used to turn on (or enable) the trace flags. A trace flag remains enabled until
it is disabled using the
DBCC TRACEOFF
statement. If the server is restarted, this trace flag will be cleared. You can
determine the status of a trace flag using the
DBCC TRACESTATUS
statement. Setting both of the deadlock trace flags
looks like this:
DBCC TRACEON (1222, -1);
DBCC TRACEON (1204, -1);
To ensure that the trace flags are always set, it is possible to make them part of the SQL Server startup in the SQL
Server Configuration Manager by following these steps:
1.
Open the Properties dialog box of the instance of SQL Server.
2.
Switch to the Startup Parameters tab of the Properties dialog box, as shown in Figure
21-3
.
