Cannot Kill Process stays in KILLED/ROLLBACK status

Question

Cannot Kill Process stays in KILLED/ROLLBACK status

krishnaprasad14

SSCrazy

Points: 2903
More actions
April 26, 2012 at 5:16 am

#100024

Hi I have killed one spid bu its still showing this KILLED/ROLLBACK status
Sugest me to resolve this with out restating the server please.....
[Foun a link here but it's not resolved my issue http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/71d3cd0b-ce4d-4202-8e3b-38b38da90a5b%5D
Thank you in Advance .

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

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

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

Wait. It's rolling back and has to finish the rollback. Don't restart SQL, that will just result in the rollback continuing after the restart, possibly with the database offline.

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

krishnaprasad14 SSCrazy Points: 2903 More actions · Answer 2

Thank you for replay

It's stil showing same. last four hours this is in same situation......

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You have to wait for it to finish.

If you kill the process again it will show you the rollback completion % and estimated time.

What exactly was the process that you killed doing?

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

krishnaprasad14 SSCrazy Points: 2903 More actions · Answer 4

DBCC CheckDB (DBName , REPAIR_ALLOW_DATA_LOSS) .......

And event type is Language Event

Last wattype is LOGMGR

Thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Wait for it to finish the rollback.

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

krishnaprasad14 SSCrazy Points: 2903 More actions · Answer 6

krishnaprasad14

SSCrazy

Points: 2903

April 26, 2012 at 7:31 am

#1479707

OK Tahnk you. Will update the situation ....

krishnaprasad14 SSCrazy Points: 2903 More actions · Answer 7

Still this is in same state .....KILLED/ROLLBACK status

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

GilaMonster (4/26/2012)
You have to wait for it to finish.
If you kill the process again it will show you the rollback completion % and estimated time.

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

krishnaprasad14 SSCrazy Points: 2903 More actions · Answer 9

Issue is still in same state.

1. DBCC OPENTRAN is showing this results....

Transaction information for database 'master'.

Oldest active transaction:

SPID (server process ID): 56

UID (user ID) : -1

Name : ChangeDbState

LSN : (628:376:1)

Start time : Apr 26 2012 10:49:29:140AM

SID : 0x01050000000000051500000079e3fc5354190e29be043e32823f0000

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

2. DBCC INPUTBUFFER (56)

DBCC CheckDB (QFM162RES14 , REPAIR_ALLOW_DATA_LOSS)

3. KILL 56

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

Thank You

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 10

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 Default port Points: 1486 More actions · Answer 11

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 SSCrazy Points: 2903 More actions · Answer 12

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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%5B/quote%5D

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 SSC Guru Points: 1004406 More actions · Answer 14

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.

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