One of my customer reported the error "Transaction (Process ID xxx) was deadlocked on (xxx) resources with another process and has been chosen as the deadlock victim. Rerun the transaction." In my customers case the deadlock error was :

1Transaction (Process ID 75) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

As it appears that this is a resource deadlock and one resource is waiting for sub resources in a cyclic manner for long and the resource which is has done the least amount of work will be marked as deadlock victim by the SQL Server 2005. The resource can be block due to communication buffer or waiting threads or combination of both.

Typically the Trace Flag 1204 Report generates the results depending on the resource involved but the somehow the report format remain same.

To start Trace Flag 1204, type the following command - be advised that here we are starting the sqlserver instance with the Trace Flags:

C:\Program Files\Microsoft SQL Server\binn\sqlservr   T1204  T3605

The other way to enable the Trace Flags is to add them in the startup properties within the Enterprise Manager.

To see which Trace Flags are enabled type:

dbcc tracestatus(-1)