MyDoggieJessie (3/12/2014)
If you want to see more detailed information on what caused the deadlock and which one was the deadlock victim, you can have them written to the error log - you can do this by turning on the proper trace flag:DBCC TRACEON(-1, 1204, 3605)
3605 isn't needed. That traceflag just causes some commands like DBCC PAGE to write their output into the error log.
1204 is for SQL 2000 and below, it doesn't give as much information as the newer traceflag does.
For deadlock monitoring on SQL 2005 and above:
DBCC TRACEON(1222,-1)
Or the system health event session on SQL 2008 and above.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability