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.
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.
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.