Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Cannot Kill Process stays in KILLED/ROLLBACK status Expand / Collapse
Author
Message
Posted Friday, April 27, 2012 6:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 3:48 AM
Points: 2,274, Visits: 767
ive had this a few times and never got a sucessfull explanation for it ...

it normally happens to me if i'm running an xp in 2000 (such as xp_cmdshell) if the xp crashes sql2000 doesn't seem to want to do anything with it.

i've always had to reboot (despite searching for an eternity for the fix)

perhaps since then someone has found a fix


MVDBA
Post #1291443
Posted Friday, April 27, 2012 6:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 18, 2013 6:53 AM
Points: 60, Visits: 122
I also encounterd same issue we left the killed\rollback transaction for a weak but no progress at all than we decided to recycle the SQL services. After restart that spid was no more exist.
Post #1291446
Posted Monday, April 30, 2012 4:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:30 AM
Points: 162, Visits: 728
Thank you very much for all .....

Issue is resolved and Now This DB is up and running .........

http://www.sqlskills.com/BLOGS/PAUL/post/Do-transactions-rollback-when-DBCC-CHECKDB-runs.aspx

Krishna
Post #1292374
Posted Monday, April 30, 2012 4:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 41,530, Visits: 34,446
krishnaprasad14 (4/30/2012)
Thank you very much for all .....

Issue is resolved and Now This DB is up and running .........

http://www.sqlskills.com/BLOGS/PAUL/post/Do-transactions-rollback-when-DBCC-CHECKDB-runs.aspx


That's talking about the snapshot creation upon running a normal checkDB. If you run checkDB with any of the repair operations and cancel that, it really does have to roll back any of the repairs it did.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1292385
Posted Monday, April 30, 2012 6:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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.

--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1292455
Posted Monday, April 30, 2012 6:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
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

Post #1292473
Posted Sunday, May 19, 2013 11:40 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
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?

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/

Post #1454354
Posted Sunday, May 19, 2013 11:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
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/

Post #1454356
Posted Sunday, May 19, 2013 12:37 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 41,530, Visits: 34,446
SQL error log. Recovery process is logged in there


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1454362
Posted Sunday, May 19, 2013 2:31 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
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/

Post #1454368
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse