Session stuck in killed\roll back

Question

Post reply

Session stuck in killed\roll back

Feivel

SSCertifiable

Points: 7524
More actions
January 15, 2019 at 11:02 am

#409433

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.
OS & SQL version
Windows Server 2016 Datacenter
RAM: 14 GB
RAM Allocated to SQL = 10 GB
4 cores
SQL Server Enterprise Edition (2016) sp2-cu3.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

goher2000 SSCertifiable Points: 5479 More actions · Answer 1

umm... let it finish

KILL 69 WITH STATUSONLY

Transaction rollback in progress. Estimated rollback completion: xx% Estimated time left: xx seconds.

Johan_1975 Right there with Babe Points: 724 More actions · Answer 2

Hello,
It's currently one of the worst issues I have to handle..
I didn't saw your stored procedure but imho I think you first need to be sure with a sp_whoisactive (or sp_who2) than your query is not blocked by another process.
In a second time I would check the execution plan ...
I read somewhere than when you kill a process, the rollback doesnt use MAXDOP, so it can be way longer than the initial query, it is better to let it finish most of the times...

Johan Jezequel
** Sorry for my english, I promess you I do my best **

Feivel SSCertifiable Points: 7524 More actions · Answer 3

goher2000 - Tuesday, January 15, 2019 11:21 AM
umm... let it finish
KILL 69 WITH STATUSONLY
Transaction rollback in progress. Estimated rollback completion: xx% Estimated time left: xx seconds.

Here is what I get which isn't very useful.

Feivel SSCertifiable Points: 7524 More actions · Answer 4

Johan_1975 - Tuesday, January 15, 2019 11:45 AM
Hello,
It's currently one of the worst issues I have to handle..
I didn't saw your stored procedure but imho I think you first need to be sure with a sp_whoisactive (or sp_who2) than your query is not blocked by another process.
In a second time I would check the execution plan ...
I read somewhere than when you kill a process, the rollback doesnt use MAXDOP, so it can be way longer than the initial query, it is better to let it finish most of the times...

No it isn't blocked by any other session. It is actually using maxdop = 1. Now, I do see other sessions also being stuck in the killed\rollback state.

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Feivel - Tuesday, January 15, 2019 11:02 AM
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.
OS & SQL version
Windows Server 2016 Datacenter
RAM: 14 GB
RAM Allocated to SQL = 10 GB
4 cores
SQL Server Enterprise Edition (2016) sp2-cu3.

You sure it isn't something with a remote query, linked server? The Wait is related to an OLEDB provider.

Sue

Johan_1975 Right there with Babe Points: 724 More actions · Answer 6

You have to be patient...
My colleague had the same problem a few hours ago, and I told him to close SSMS to try to stop the process, but it was a SELECT query (on a huge table).
When it's an INSERT / DELETE query, close the SSMS dosnt have any effect.

Johan Jezequel
** Sorry for my english, I promess you I do my best **

Feivel SSCertifiable Points: 7524 More actions · Answer 7

Sue_H - Tuesday, January 15, 2019 12:01 PM
Feivel - Tuesday, January 15, 2019 11:02 AM
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.
OS & SQL version
Windows Server 2016 Datacenter
RAM: 14 GB
RAM Allocated to SQL = 10 GB
4 cores
SQL Server Enterprise Edition (2016) sp2-cu3.
You sure it isn't something with a remote query, linked server? The Wait is related to an OLEDB provider.
Sue

It is definitely coming from a web application so I assume it is a remote query. Also, when I extracted the sql script for that stored proc I do see calls being made to a [SalesForce] linked server to pull data using dbamp. Also, noticed memory exceptions thrown in the event viewer for .NET Runtime errors. I am suspecting that these low memory conditions might be a cause of these sessions stuck in the rollback phase or maybe I missing something more crucial here.

goher2000 SSCertifiable Points: 5479 More actions · Answer 8

try using kill UOW

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2016

sys.dm_tran_active_transactions

Feivel SSCertifiable Points: 7524 More actions · Answer 9

goher2000 - Tuesday, January 15, 2019 12:31 PM
try using kill UOW
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2016
sys.dm_tran_active_transactions

No luck.

Sue_H SSC Guru Points: 90860 More actions · Answer 10

Feivel - Tuesday, January 15, 2019 12:58 PM
goher2000 - Tuesday, January 15, 2019 12:31 PM
try using kill UOW
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2016
sys.dm_tran_active_transactions
No luck.

Killing by UOW works for orphaned spids, maybe others but yours isn't orphaned.
You used to be able to kill those (remote query spids) by finding the kpid in SQL Server and killing the matching PID process with process explorer, task manager. Haven't had do it for years so don't know if it still works.

Sue

Feivel SSCertifiable Points: 7524 More actions · Answer 11

Sue_H - Tuesday, January 15, 2019 1:12 PM
Feivel - Tuesday, January 15, 2019 12:58 PM
goher2000 - Tuesday, January 15, 2019 12:31 PM
try using kill UOW
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2016
sys.dm_tran_active_transactions
No luck.
Killing by UOW works for orphaned spids, maybe others but yours isn't orphaned.
You used to be able to kill those (remote query spids) by finding the kpid in SQL Server and killing the matching PID process with process explorer, task manager. Haven't had do it for years so don't know if it still works.
Sue

I agree these aren't orphaned spids. I can get the kpid but how do I match with the pid.

Sue_H SSC Guru Points: 90860 More actions · Answer 12

Feivel - Tuesday, January 15, 2019 1:18 PM
Sue_H - Tuesday, January 15, 2019 1:12 PM
Feivel - Tuesday, January 15, 2019 12:58 PM
goher2000 - Tuesday, January 15, 2019 12:31 PM
try using kill UOW
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2016
sys.dm_tran_active_transactions
No luck.
Killing by UOW works for orphaned spids, maybe others but yours isn't orphaned.
You used to be able to kill those (remote query spids) by finding the kpid in SQL Server and killing the matching PID process with process explorer, task manager. Haven't had do it for years so don't know if it still works.
Sue
I agree these aren't orphaned spids. I can get the kpid but how do I match with the pid.

Task manager or process explorer...whichever one you use. Task manager, you may need to add the PID to the view.

Sue

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 13

This sound symptomatic of a problem we had last year. Simple SELECTs getting stuck and going into a KILLED/ROLLBACK that couldn't be killed. It crippled us a couple of times a day.

It turned out to be the connections that the apps were using. They all had MARS (Multiple Active Result Sets) enabled even though the default in .NET isn't supposed to do that. We fixed those, the problem went away immediately, and we've not had the problem since. How did it start? I don't know for sure but I suspect it reached some sort of a "tipping point" due to the ever-increasing size of the data an WHAM!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Feivel SSCertifiable Points: 7524 More actions · Answer 14

Jeff Moden - Wednesday, January 16, 2019 8:49 AM
This sound symptomatic of a problem we had last year. Simple SELECTs getting stuck and going into a KILLED/ROLLBACK that couldn't be killed. It crippled us a couple of times a day.
It turned out to be the connections that the apps were using. They all had MARS (Multiple Active Result Sets) enabled even though the default in .NET isn't supposed to do that. We fixed those, the problem went away immediately, and we've not had the problem since. How did it start? I don't know for sure but I suspect it reached some sort of a "tipping point" due to the ever-increasing size of the data an WHAM!

How did you end up resolving the same? I am not sure if my case is relevant to yours but as I mentioned previously do we really know if resource contention would be a reason why these sessions were stuck in that state because I noticed low memory warnings on that server. I am not sure if I can really relate it to anything at this point as I am not sure what answer to provide to the the business folks at my workplace.