• Adam Thurgar

Deadlock Trace Flags

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.

Recent Posts

See All

Review your trace flags

Recently I was working on a SQL Server that had the following trace flags enabled. 1204 and 1222 for deadlocks. 1117 uniform tempdb allocation 1118 uniform extent allocation instead of mixed 661 disab