Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Kill SPID (SQL Spackle)

By Kenneth Fisher,

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

Total article views: 2769 | Views in the last 30 days: 53
 
Related Articles
FORUM

Transaction block and Identity column

The scope of identity column value in a transaction block.

FORUM
FORUM

Resolving Transaction Blocking Issues?

Transaction Blocking

FORUM

Session blocking performance

SQL 2005 Performance drop due to blocking session

FORUM

Definition of Session and Connections

Definition of Session and Connections

Tags
administration    
kill    
sql spackle    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones