Finding SQL Server deadlocks

A deadlock occurs when two processes are each waiting for the other’s locked resource and neither process can continue because the other process is preventing it from getting the resource.
The following message is given when the server resolves a deadlock situation:
Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
SQL Server usually chooses the thread running the transaction that is least expensive to undo as the deadlock victim.
You need to find the deadlock situations and then investigate the problem.
Trace flags:
The 1204 trace flag and –T1204 startup parameter collects information about the process and the resources when the deadlock is encountered.
The 3605 trace flag or –T3605 startup parameter writes this information to the SQL Server error logs.
The 1205 trace flag and –T1205 startup parameter collects information every time that SQL Server checks for a deadlock, so you do not have to use the -T1205 startup parameter.
The 1222 trace flag and –T1222 startup parameter returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format.
For more information about enabling, disabling and checking for active trace flags read this blog post:
How to enable and disable trace flags, or check for active trace flags in SQL Server
You can gather information about the deadlocks immediately with the following code that enables the deadlock global trace flags.
dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go

or you can add -T1204 and -T3605 startup parameters that will take effect when SQL Server is stopped and then re-started:
right-click on the SQL Server service using Configuration Manager, select Properties and the Advanced tab, and add:
;-T1204
and
;-T3605
to the Startup Parameters entry

You can check for active trace flags on the instance with:
DBCC TRACESTATUS

When a deadlock occurs, the information about the deadlock will be captured in the SQL Server Error Log.
SQL Profiler trace:
SQL Profiler can obtain the full statements in addition to the execution plans of the statements. SQL Profiler “Deadlock” event corresponds to the -T1204 flag, and “Deadlock Chain” event corresponds to the -T1205 flag.

In SQL Server 2005 and SQL Server 2008, the Deadlock graph trace feature of SQL Server Profiler provides visualization of deadlocks.
Server Side Trace:
Collect following additional EventNumber and Event information to capture the deadlock details with server side trace:
EventNumber: 25 Event: Lock:Deadlock
EventNumber: 59 Event: Lock:Deadlock Chain
EventNumber: 22 Event: ObjectID