Cannot Kill Process stays in KILLED/ROLLBACK status

Question

Cannot Kill Process stays in KILLED/ROLLBACK status

Viewing 8 posts - 16 through 22 (of 22 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Jeff Moden (4/30/2012)
There's a good chance that when a "rollback" hangs up like this, that it's waiting on something in another spid to complete. It's sometimes difficult to find but you need to find that spid and kill it in order for the spid you killed to complete its rollback.

If it was a normal query, I'd agree with you, but to run checkDB with a repair option, the DB would have had to be in single user mode. Could have been waiting for a non-SQL resource (eg IO)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I had 5 SPIDS that run SSIS Packages.

Each process was stuck on the Step that executes an OPENQUERY that does a SELECT COUNT(*) to get the record counts in AS400 Tables.

I stopped all of the jobs but the SPID is still executing the OPENQUERY.

I killed on of the SPIDS but when I execute the KILL 54 WITH STATUSONLY I get :

SPID 54: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

It has been stuck for about 30 minutes.

There are no connections to the AS400.

This thing is not going to complete the Rollback.

What can I do?:unsure:

I'm currently copying the backups to another Server and plan on restoring them.

I do not want to restart the Service and have the Database go into recovery.

Isn't there a command to take offline and ignore open transactions or something to that?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

When a Database is in recovery I seem to recall that you check the progress in the Event Viewer Application Log?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

SQL error log. Recovery process is logged in there

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Thanks.

I'm almost certain that the recovery status is available in the Application Event Viewer as well.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Perkins Ten Centuries Points: 1358 More actions · Answer 6

I've been waiting for the process to finish for several hours now. When I first ran the kill command after seeing the SPID in rollback status, I got the following message:

SPID 51: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

It has been much longer than that and I was thinking of putting in a change request to restart the server since it is production. I'll do what Gail says and wait some more I guess:-D

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

Sean Perkins (1/4/2016)
I've been waiting for the process to finish for several hours now. When I first ran the kill command after seeing the SPID in rollback status, I got the following message:
SPID 51: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
It has been much longer than that and I was thinking of putting in a change request to restart the server since it is production. I'll do what Gail says and wait some more I guess:-D

I've seen this before. One thing that might help ...

Where is SPID 51 coming from? I've seen cases where the rollback would never complete until I closed that connection. For example, SPID was related to a user trying to pull data using Excel. Closing Excel on the user's machine solve the problem.

This may not help, but it's worth looking into.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

In my experience, the 0% complete, 0 seconds remaining rollbacks tend to sit around forever. It would be waiting for an external resource, so do check, but those ones alone are usually safe to restart SQL to fix (only the 0% complete, 0 seconds rollbacks)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass