SQL Server Performance Forum – Threads Archive

Dead Locks and how to deal with them

Hello all, I am no expert and have just come accross my first deal lock issue on one of our servers. Is there a way to deal with dead locks automatically, or would you suggest dealing with them manually. Cheers.

You can indentify locks using sp_who or sp_who2 to find who is blocking.
Or Entreprise Mananager, but is slowly than Query Analyzer.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

depending on how bad the situation is, you may be waiting for a very long time for results of sp_who2.
I also use this select * from sysprocesses where blocked > 0
This gives the spid causing the blocking, which I then select from sysprocesses. This usually gives me enough information. Based on whats causing the lock and why, will determine what the next step is. Sometimes its quite simple and I can kill the process at the head of the chain. Sometimes not. For example if its an update process that has been running for an hour, cancelling it will likely result in an hour of rollback. Then I’d have to figure out how much longer it will run for and decide which will be faster. Chris

Thanks you guys, one last question though, when I run the following, where do I see the results. dbcc traceon(3650, 1205, -1)
Go Cheers.

Trace 3605 sends trace output to the error log. (If you start SQL Server from the command prompt, the output also appears on the screen.) so first run that TRACE then run the other trace to capture the data in SQL Log files. I think there is option to send the output to some txt file as well, which I don’t know as of now.

]]>