Mani Singh (7/9/2008)
Adding to it:sometimes, the Deadlocks might resolve by themselves in time, but if you kill a long waiting deadlock, you might end up with a Phantom Process/lock and it stays there, unless you have the option to restart the SQL Service.
For the third or fourth time, No! As I said earlier in this thread
You don't ever have to worry about killing processes involved in a deadlock. SQL has a deadlock detector built in, if it detects an unresolvable locking condition (a deadlock) it will pick one of the processes involved and automatically kill it.
The definition of a deadlock is an unresolvable locking condition. Hence left alone they will never resolve themselves. That said, you will almost never be able to kill a process involved in a deadlock. The SQL deadlock detector is a lot faster than you are.
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