Folks,

Please suggests me several effective and quick way to detect and solve application deadlock.

for me, I used the sp_who2 to detect deadlock and then kill the victim

better and more effective way? Thanks.



------------------
Bug explorer/finder/seeker/locator
------------------

See Books Online under the subject "Detecting and Ending Deadlocks". There is quite a bit of material regarding deadlock resolution.

Are you sure you're not referring to regular blocking?

To identify and capture deadlocking I like to use DBCC TRACEON (1204) or (1205) run DBCC TRACEON (3605,1204,-1) to dump the deadlock data to SQL Error Log. This will give you the type of locking going on and command. Make sure to turn this feature off (DBCC TRACEOFF) when you are done. You can also query the syslockinfo to view lock details at a glance. After finding the root causes of the blocking or deadlocks you should recommend to the developers to alter thier code or transactions to minimize future incidents.

SQL Server is really good about handling deadlocking it usually chooses a victim and kills that process.

Raulie

Take a look the following articles...
http://www.sql-server-performance.com/deadlocks.asp
http://support.microsoft.com/kb/224453


Mohammed U.