How to stop a run-away SQL transaction

The Problem: Recently was testing my web-app against a SQL-Server 2008 database and got a time-out expired message from the SQL database. No matter what I did after that it just did not want to drop the transaction and I continually got the time-out. I needed to find and stop the SQL transaction.


The solution: Sign onto the SQL-Server with the Enterprise Manager.

Execute sp_who2

and look for rows that are blocked on another connection (the BlkBy column will have the spid of the blocking connection in it).

Now kill the culprit:

kill xx

(xx = spid).

Print | posted @ Thursday, July 2, 2009 11:05 AM

Comments on this entry:

Gravatar # re: How to stop a run-away SQL transaction
by vijay @ SSH command in unix at 7/30/2012 4:29 AM

Instead of killing the blocked transactions, is there any other way to wait for the transactions to complete. I mean the webapp should not throw time out error but should wait for the other transactions to complete.

Unix and linux commands
Gravatar # re: How to stop a run-away SQL transaction
by kannan perumal at 2/19/2013 1:14 AM

good work. it works fine. thanks
Gravatar # re: How to stop a run-away SQL transaction
by Rohit at 10/2/2015 12:04 PM

It's not easy but it's possible, here are some tips to manage that.
Post A Comment
Title:
Name:
Email:
Comment:
Verification: