SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cannot Kill Process stays in KILLED/ROLLBACK status


Cannot Kill Process stays in KILLED/ROLLBACK status

Author
Message
MVDBA
MVDBA
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 860
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
rraja
rraja
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 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.
krishnaprasad14
krishnaprasad14
SSC-Addicted
SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)

Group: General Forum Members
Points: 411 Visits: 1022
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86971 Visits: 45267
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, 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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85573 Visits: 41082
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86971 Visits: 45267
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
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10154 Visits: 4894
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
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10154 Visits: 4894
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/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86971 Visits: 45267
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
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10154 Visits: 4894
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search