Kill a blocked process in a database

Question

What is the best solution if we see a lot of blocked processes in a database?

Answer 1

Some blocking is normal, the question is if the blocking is causing issues preventing queries from completing in a timely manner or worse causing deadlocks. What you need to do is identify what is causing the blocking. Microsoft had some good tools to troubleshoot this.

This post should get your started or here

Answer 2

Temporary Solution

Use sp_who to find the spids and kill spid (ie kill 59 where 59 is the spid of the blocking process) to kill the process.

Real Solution

This will sort the problem but its not going to stop it from happening again. To do that you need to look at your code. I would suggest at a minimum that you use WITH NO LOCK on any select you are doing to reduce the potential for locks.

Also, you could refactor your code so that you only are accessing the bare mininum number of times. Consider copying to a temp (# or ##) table to do detailed processing and then copy back to position if needed. This particularly applies where the purpose is to prep data for reporting. It is better to pull the data once and do any additional processing away from the main tables.

If this is not possible because the underlying data is changing too fast or you require changes to be applied for other purposes then use a SQL Service Broker to queue up async work items. This can be used to resolve blocking depending on your situation.

Answer 3

WITH (NOLOCK) is not necessarily a good option. This will cause the query to return uncommitted reads. On a transactional system, this may be behavior you don't want.