blocks in SQL server

There are many things that you can say about locks in SQL server. But to get to the point about locks they cause problems when a user has an exclusive lock on a record and is blocking one or more people. In my experience the whole instance of a blocking problem can work like this. Phone ring user says “The system is really slow, I have been waiting for ever for this data to be displayed”.

You being the SQL server administrator open management studio and from a query window you run “sp_who2”.



image used from http://ravisql2k5.blogspot.com/


Look at the BlkBy column, this will give you the spid (server process ID) that is causing you other users problems. Now it’s decision time, sq_who2 gives you a little more information about the offending spid. You can look at the Login, and possibly get some information from the host-name. Lets say you recognize the Login, you then call the user who is locking everything and see if you can kill his process.
Lets say you don’t recognize the user. You may want to take some time and do more research on what this process is, but you still have the user on the phone. Either way I would say most of the time the solution is killing the process. In your query window type kill and the spid that is blocking progress.
Example if you want to kill spid 55 you type “kill 55” without the quotes. Finally run sp_who2 to make sure everyone is playing fare and not blocking anyone else. Your upset user probably will say “hey I got my data, what did you do?”. At this point you can either be humble hero or all powerful god it’s up to you.