A lot of SQL troubleshooting starts like this:
The “spootoo” procedure brings back a list of SPIDs and includes information on which SPIDs are blocking. You can quickly track back to a problem SPID by following the information in the “BlkBy” column, which shows the SPID blocking a row.
Once you have the SPID, you probably want to know what query is executing, in order to work out why things are blocked. You can do that by passing the SPID into the following query:
This is a step I often recommend people add to their troubleshooting process (rather than running a KILL against the SPID, which can be a little rash!)
I also have a useful script for filtering and sorting sp_who2 queries.