Detecting and resolving deadlocks in SQL Server can be difficult. By default deadlock detection is not enabled. Deadlocks can cause data inconsistencies and performance problems. To detect deadlocks, trace flags 1204 and 1222 can be enabled. I recommend that these trace flags be set as part of the database engine service startup options (-T).

When deadlocks occur, trace flag 1024 and 1222 return information in the SQL Server 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. Both trace flags can be enabled to get two representations of the same deadlock.