Self blocking session could not be killed

Question

Self blocking session could not be killed

sqlnes

Mr or Mrs. 500

Points: 595
More actions
February 10, 2014 at 6:43 pm

#148421

I got a session that is in suspended state and the blocking spid shows from the same session, say, spid 107 blocked by spid 107. I use sp_who2 and there is only one row returned. it used rarely cpu nor io. I tried to kill it but it now keep in "KILL/ROLLBACK" state. not sure how to get rid of it. Could anyone help on this?
Thanks,
V

Viewing 9 posts - 1 through 8 (of 8 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Now you just wait it out. The spid is in a rollback state and all you can do is wait for it to finish.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlnes Mr or Mrs. 500 Points: 595 More actions · Answer 2

ok. and that's what i'm doing now. it has been 2 days...before i killed it, it had run for 18 hours...

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

Wait. Rollback usually takes longer than the roll forward. DO NOT restart SQL, otherwise the rollback will be restarted after the instance restarts, probably with the database unavailable to anyone.

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

sqlnes Mr or Mrs. 500 Points: 595 More actions · Answer 4

thx for reminding.

I will not restart sql server for this. It looks like no other session as been affected by this session. however, the weird thing is that the session has used zero CPU & IO in sp_who2...

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

If you run a KILL for that session, what's the output that you get?

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

sqlnes Mr or Mrs. 500 Points: 595 More actions · Answer 6

when i just tried to kill the session again. it showed,

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

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

Ok, in that case (0%, 0sec) it's probably a stuck rollback (they happen from time to time, it'll be trying to get something that it can't get) and it's usually safe to restart SQL. Do it next time you have a maintenance window, unless this rolling back session is causing problems.

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

sqlnes Mr or Mrs. 500 Points: 595 More actions · Answer 8

sqlnes

Mr or Mrs. 500

Points: 595

February 12, 2014 at 7:08 pm

#1688586

Got it! thanks Gail!