One of the session that is executing a stored proc from an application is stuck in the killed\rollback phase. Arguably, it shouldn't be this long for the sproc to rollback and it has been stuck there for an eternity. Basically, the sproc is a bunch of select with unions and I am curious as to why this is holding up that long. As far as the waits are concerned below is a snippet of what I see that it is waiting on. I would like to understand how am I going to get rid of this w/o restarting SQL services and most importantly what basically can be done in order to avoid this situation either from the application side or from the SQL side. Let me know if anything else is needed. Also, these stored procedure is using [SalesForce] as linkedserver using DBAmp to fetch the data...would this be a cause and how to overcome the same.
Depending on how long
an eternityis here, it's possible it's hung forever.I previously worked in an environment where we routinely pulled data into SQL Server from a mainframe application. Periodically, the mainframe would unexpectedly terminate a connection, but would not communicate anything back to SQL Server, which would happily sit in an 'Executing' state waiting for the query results. The next day, when the same job would run, the not-executing-executing-query would block the new instance and throw an error.
KILLing the undead connection would allow the new instance to run, but the old instance would stick inKILLED\ROLLBACKuntil we restarted SQL Services.Since the zombies weren't interfering with anything, we'd usually let them sit until the monthly maintenance window.
Before implementing this work-around, on several occasions we had our mainframe server engineers verify for us that as far as the mainframe was concerned there really was no active connection. You should check the
SalesForceside and see if there's any activity there.