It's unfortunate but sometimes you need to stop a transaction. It might be blocking other transactions or consuming too many resources. Fortunately killing a transaction is easy.
KILL session_id
If you have killed a session with a large transaction you can get a feel for how far along in the roll back process it is by using this command:
KILL session_id WITH STATUSONLY
If killing a session is easy, finding out what session_id you want to kill is not. The first way I learned (and is compatible all the way back to at least SQL 2000) is to use sp_who.
sp_who
The output looks like this:
The column spid is the session_id and the blk column is the session_id that is blocking this particular session's transaction. In the example above session 53 is being blocked by session 52. I should also mention that sessions below 50 are session id's controlled by the system and represent background processes. You should rarely if ever need to look at these particular session_ids.
Of course you don't always need to find a blocking session which leads us to sp_who2.
Here SPID is still the session_id and BlkBy column is the session_id that is blocking the session. In addition we get the additional columns CPUTime (cumulative CPU for the session) and DiskIO (cumulative IO for the session). This helps us see if a session is being a resource hog or not. If you run the SP multiple times you can begin to see how many resources are currently being used. The down side of this particular SP is that unlike sp_who it is undocumented and unsupported.
Both of these stored procedures have one major drawback that becomes very obvious once you have hundreds of sessions connected to the instance. There is no easy way to restrict the output so you can end up scrolling up and down a list of hundreds of connections to find a blocked connection which can be somewhat cumbersome. It would be quite a bit easier if we could use a query instead of a stored procedure wouldn't it? Since SQL 2005 we have had DMO's available to us and can find blocking connections using the following query.
SELECT blocking_session_id, * FROM sys.dm_exec_requests
I pulled blocking_session_id to the front because it is usually far enough to the right that it won't show up in the image. The columns here are a little bit more obvious and we get a lot more information than the previous methods. Columns to pay attention to include session_id, blocking_session_id, start_time, cpu_time, total_elapsed_time, reads, writes, and logical_reads. You can also tie this in with sys.dm_exec_sql_text to get the currently running sql command.
If you want some additional reading to do I've written in more detail about where to see transactions and what they are doing and how to deal with blocking negative session id's.
