April 5, 2006 at 9:01 am
i just had a deadlock, and using activity monitor i killed off one of the processes and that solved it, but, for future reference, how do you identify which processes are caught in deadlock in activity monitor?
i just see whats running, and on what database. i kill off one of the processes that i dont need if there is a deadlock. i think that is a bit general and i could be killing off processes unnecessarily. how do i make sure im only killing off a process i have to kill off?
April 5, 2006 at 9:19 am
The blocking and blocked by columns show the spids of the processes that are blocked by the process or are blocking the process. If you trace this chain to the lead blocker, you have the right one.
In other words, you may find spid 45 showing spid 65 blocking it and spid 65 showing spid 45 blocking it, etc...
April 6, 2006 at 2:42 am
The moment a deadlock situation occours, SQL server breaks it automatically. DBAs hardly do anything to break a deadlock.
Your case may be an issue of blocking and not deadlock, because the moment SQL server receives any DEADLOCK notification, it simply kills one of the SPID(victim) and rolls back all the work done by this SPID.
April 6, 2006 at 3:43 am
and whats the difference betweeen blocking and deadlock? is it that deadlock will never end unless sql server or the admin kills a process, and blocking will end when the blocking process ends? because i had aprocess being blocked for over 5 hours. i would have thought of that as a deadlock
April 6, 2006 at 8:39 am
the basic diff is this:
blocking - one process is waiting on another process. that other process could be doing whatever, not related o what the first process is doing
Deadlock - one process is doing on thing (ProcessA) that is waiting on ProcessB. ProcessB is on the other hand waiting on ProcessA. Because they are waiting on each other it's a deadlock.
Curtis Smith
SQL Server DBA
Well in worked in Theory ...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply