track down the queries that are causing the deadlocks

A deadlock occurs when two processes are trying to update the same record or set of records, but the processing is done in a different order and therefore SQL Server selects one of the processes as a deadlock victim and rolls back the statements.

turn on the trace flag to find the deadlocks

DBCC TRACEON (1204)

SQL Server Error Log

To view the SQL Server error log

    In Object Explorer, expand a server, expand Management, and then expand SQL Server Logs.

    Right-click a log and click View SQL Server Log.

You can turn off the trace flag any time by simply issuing the

check the status of the trace flag using the

DBCC TRACESTATUS (1222, -1);
GO

 -1
    Displays the status of trace flags that are enabled globally.
    
    
start:

DBCC TRACEON(1222,-1)

stop:

DBCC TRACEOFF (1222,-1)

之後在 log file 找 "deadlock victim" 要它的 process 值

用 process 的值找 spid

Trace Flags

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

1222

Scope: global only
Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.