0

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.

enter image description here

Thom A
  • 88,727
  • 11
  • 45
  • 75
Feivel
  • 111
  • 4
  • Run `KILL 69 WITH STATUSONLY`. That'll tell you about how much time is remaining on that. Emphasis on *about*. Remember `ROLLBACK` is usually [single threaded](https://blogs.msdn.microsoft.com/psssql/2008/09/12/sql-server-2000-2005-2008-recoveryrollback-taking-longer-than-expected/). That wait is coming from the linked server (OLE DB data source). Not sure what DBAmp does, but sure... it could be an issue too. – S3S Jan 16 '19 at 19:30
  • I already tried out those options but those didn't work and that is why I thought of asking if someone in the community might have come across a similar issue in the past. Also, while reviewing the event log I noticed quite a few warnings of memory being low on the server and some .NET runtime termination errors caused due to lack of memory. I am suspecting this could be related as there were other user sessions also which were stuck in that state and was waiting on the same wait_type. – Feivel Jan 16 '19 at 19:46
  • Memory contention would certainly affect a rollback. Have you ran sp_WhoIsActive to see what else is going on? – S3S Jan 16 '19 at 19:49
  • Unfortunately, no as I was more involved with this spid but nevertheless the waits were exactly some...the sproc would have been different but it would still be doing some kind of fetching via linkedserver. – Feivel Jan 16 '19 at 19:58

1 Answers1

0

Depending on how long an eternity is 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 in KILLED\ROLLBACK until 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 SalesForce side and see if there's any activity there.

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35