Thursday, 3 April 2008

Kill SQL Deadlock

Deadlocks strangely I’m not talking about management meetings, I’m talking about SQL (Structured Query Language) so here is the deal just like when two managers can not agree on something it can become necessary to stop one so that things can continue normally.

If your unlucky enough not to have any monitoring software you might not even know one has happened until one of two things a lovely blue screen happens to you SQL server or two then you have high CPU load.

Most common is the high CPU load this at least lets you found out what happened without having to check debug logs and I’m sure I’ll get around to them in another posting.

If your SQL server is still alive you’ll be able to get some more explanations as to what is going on by using query analyzer, if you have many instances on your server you will have to run the following commands on each server till you find the problem instance.

So the command you want to run in the instance is

sp_who2

This will give a list of processes and what you are looking for is BlkBy column as if you see a number there then that is the process id that is locked, now you can find out more about what it was doing by running and inputbuffer command for the moment we’ll pretend that my locked process is 57 then I can see what it was doing by typing.

dbcc inputbuffer (57)

This information is only really good to developers to debug to you might not even be interested in it, what most likely is of interest to you is how to kill it this can be done my using the kill command simple really.

kill 57

Now after that kill command has run check the server with the sp_who2 again as there might have been more than one deadlock, once you happy you’ve resolve your deadlocks you should find your server goes back to normal.

Well that’s your tip for the day.

No comments: