Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I'm an accidental DBA and still learning.
When I see from DMV:s or Activity Monitor that some process is blocking another process, what should be done?
Should I simply kill those processes or is there a way to "release" or maybe preemptively preempt this blocking?
Sometimes, ultra violence is the answer
Anyway, a block is just a lock that goes on for too long. "Too long" is variable of course. if you see blocking via sysprocesses or the DMVs then it's almost always "too long" as blocks should be transient and very short
If you do have measurable blocking, then you need to investigate why eg long running transaction
Despite this question having already being answered and the answer accepted, I'm going to try and make a case for the contrary approach...
DO NOT EVER BLINDLY KILL A SPID
Unless you know what work that SPID has done, you have no understanding of the scale of the rollback you're about to initiate.
The worst kind of blocking chains are those which have unexpectedly brought your 24/7 system to its knees. 99% of the time, the query at the head of that blocking chain is going to be a plain old select query gone awry, queueing up any and all write activity behind it. In these circumstances, a KILL will save the day.
The other 1% of the time, that rogue query will be the last step in a long running transaction that will take as long to rollback as it did to get to the stage it's at now. Taking a moment to understand how you got here will 99% of the time be unnecessary. 1% of the time, you could be saving minutes/hours/days of downtime.
Blocking, in and of itself, isn't bad.
The problem comes when you have performance issues from long-running transactions that are blocking other transactions.
In this case, what I would do is to try to identify any long-running queries/transactions and try to fix those problems.
If you're having performance issues, try to fix the root cause, not the symptom.
Having said that, if you're having an issue on production and you absolutely need to stop it, use the KILL routine. (as gbn mentioned).
Locking is normal. It happens all the time with conflicts. It's the constant deadlocking that is an issue, as the database engine chooses a victim process to kill and rollback the transaction. Statistics prove that this is bound to happen, but if it is happening constantly then you need to drill down into the causes and rewrite queries and code logic.
