How to kill a frozen linked sql server connection

So I recently setup a Linked Server in our SQL database that connects to some sort of version of IBM’s DB2.

I made the mistake of querying a ginormous table.  After 30 minutes or so, I cancelled the query.  The query wouldn’t cancel so I closed the window in SQL Server Management Studio (SSMS).  I opened a new window and proceeded to query again with an added where clause.  I repeated this a few times with more and more restrictive where clauses.  After a while I closed SSMS and reconnected from a different machine.  I checked the activity monitor in SQL and saw that I had 5 connections.  I proceeded to “Kill” all of them.

Then this happened.
image

All the processes where stuck in KILLED/ROLLBACK and had a wait of PREEMPTIVE_OLEDBOPS.

At this point most SQL experts will blindly give you the advice that you should just “Wait” until the rollback is complete.  However, this was a linked server.  I wasn’t doing anything but select data from it.  I waited an hour or 2 an still nothing.  I checked the rollback status of each connection and each was at 0%.  Also, the CPU was pegging on the database server.  What was I to do?

Well the solution for me was to download TCP View from Microsoft.
http://www.google.com/search?q=tcp+view&btnI

I opened TCP View up and found the connections that weren’t working.  I killed the first one and the rest just disappeared.  My CPU went from almost all down to 2%.  (Note: It gave me a different machine name in TCP View because the machine in question had multiple DNS names that went to the same IP Address)

An enterprise level database should not allow an unreturned linked server to compromise the entire server.  If you agree, write to them here.

https://connect.microsoft.com/SQLServer/feedback/details/187192/

4 thoughts on “How to kill a frozen linked sql server connection”

  1. We used to have to restart the server each time this happened, but the combination of Process Explorer and TCPView has helped resolve the issue of hung Linked Server to Oracle connections for us, thanks for the tip!

  2. I would like to thank you for writing this post. I was suffering from this issue and I did not want to restart the server. I was in fact looking for something that could stop the connection used by Linked Server but couldn’t figure out myself.
    I found something to kill the thread with KPID but was again like hitting axe on your foot.

    This is an appropriate way to handle such a situation. I closed the connection and everything was OK.

    Once again thanks for the article.

  3. Great post. I’ve had this very issue occur as the password in Oracle expired (it took 2 weeks for them to check the account, which was my first question). This has been ameneded but I still need to kill the process.
    I have looked on TCP View (a great tool) but I can’t identify the connection. The ports and machines do not match.
    Any pointers? Thanks in advance.

  4. Great article but my execution wasn’t right, I killed the process in TCPView… :(

    Can you please put some screen grabs for help…
    “I opened TCP View up and found the connections that weren’t working. I killed the first one and the rest just disappeared. My CPU went from almost all down to 2%. (Note: It gave me a different machine name in TCP View because the machine in question had multiple DNS names that went to the same IP Address)”

Leave a Reply

Your email address will not be published. Required fields are marked *


+ 8 = sixteen

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>