Validated against: SQL Server 2008 R2.
Tracking down the reason for a SQL Server dead lock issue is straight forward enough, simply use the SQL Server Profiler and place a ‘Locks’ trace against the event ‘Deadlock graph’. Start the profiler and wait for a deadlock to occur.
If you required more information also apply a ‘Locks’ trace against the events ‘Lock:Deadlock’ and ‘Lock:Deadlock Chain’. For a richer picture of what was happening apply a ‘Stored Procedures’ trace against ‘RPC:Starting’ and ‘SP:StmtStarting’, and also a ‘TSQL’ trace against the event ‘SQL:StmtStarting’.
When a dead lock does occur a graphical representation of the lock is presented within the trace, which provides sufficient detail about the lock. The graphical representation shall most likely include a narrative stating ‘associated objid: <object id>’. To find out what the lock was against, query the sys.objects table (i.e SELECT * FROM sys.objects WHERE object_id=2121058592).
Another option if you wish to avoid use of the SQL Server Profiler is to have the deadlock event logged in the SQL Server Log. To do the latter, suggest a quick google search for ‘DBCC TRACEON(3604)’ and ‘DBCC TRACEON(1204’).