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

Self blocking session could not be killed Expand / Collapse
Author
Message
Posted Monday, February 10, 2014 6:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:44 PM
Points: 32, Visits: 316
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
Post #1540004
Posted Monday, February 10, 2014 7:35 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:04 PM
Points: 18,055, Visits: 16,086
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
Post #1540019
Posted Monday, February 10, 2014 9:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:44 PM
Points: 32, Visits: 316
ok. and that's what i'm doing now. it has been 2 days...before i killed it, it had run for 18 hours...
Post #1540035
Posted Tuesday, February 11, 2014 1:27 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: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
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 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 #1540071
Posted Tuesday, February 11, 2014 2:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:44 PM
Points: 32, Visits: 316
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...
Post #1540090
Posted Tuesday, February 11, 2014 4:19 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: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
If you run a KILL for that session, what's the output that you get?


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 #1540125
Posted Wednesday, February 12, 2014 1:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:44 PM
Points: 32, Visits: 316
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.

Post #1540576
Posted Wednesday, February 12, 2014 1:28 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: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
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 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 #1540582
Posted Wednesday, February 12, 2014 7:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:44 PM
Points: 32, Visits: 316
Got it! thanks Gail!
Post #1541002
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse