SQL Server RUNNABLE SPID - KILLED/ROLLBACK

Question

SQL Server RUNNABLE SPID - KILLED/ROLLBACK

bugg

SSCertifiable

Points: 7567
More actions
June 23, 2014 at 2:33 am

#308475

Hi All,
I had 3 spids in a runnable state from 3,2 and 1 days back. These had been caused by some SQL monitoring software. Looks like the SQL code was reading the log files.
I attempted to kill these processes but they have now fallen into a KILLED/ROLLBACK state with 0% rollback.
SPID 54: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
This is in a production environment so i can't reboot the server that easily. Does anyone have any suggestions on how to clear these?
Thanks
B

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

The 0%, 0 seconds usually require a restart of SQL (not the server) to resolve. Otherwise wait.

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

bugg SSCertifiable Points: 7567 More actions · Answer 2

GilaMonster (6/23/2014)
The 0%, 0 seconds usually require a restart of SQL (not the server) to resolve. Otherwise wait.

Thanks Gila, when you say wait how long could that be before 🙂 Or is that an open ended question?

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

bugg (6/23/2014)
GilaMonster (6/23/2014)
The 0%, 0 seconds usually require a restart of SQL (not the server) to resolve. Otherwise wait.
Thanks Gila, when you say wait how long could that be before 🙂

Depends if it's actually doing something or if it's stuck. If it's doing something, then a finite (but unknown) amount of time. If it's stuck, then forever.

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

bugg SSCertifiable Points: 7567 More actions · Answer 4

GilaMonster (6/23/2014)
bugg (6/23/2014)
GilaMonster (6/23/2014)
The 0%, 0 seconds usually require a restart of SQL (not the server) to resolve. Otherwise wait.
Thanks Gila, when you say wait how long could that be before 🙂
Depends if it's actually doing something or if it's stuck. If it's doing something, then a finite (but unknown) amount of time. If it's stuck, then forever.

Thought as much, i'll hang back a day or 2. If the SPIDs are still around I will schedule in some maintenance and restart the service. Thank you for your help on this.

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

If they aren't blocking anything, then it should be pretty safe to leave them be. Just watch that there isn't an open tran registered for any of them that could be preventing log reuse.

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

bugg SSCertifiable Points: 7567 More actions · Answer 6

GilaMonster (6/23/2014)
If they aren't blocking anything, then it should be pretty safe to leave them be. Just watch that there isn't an open tran registered for any of them that could be preventing log reuse.

No they're not causing any blocks. I ran DBCC OPENTRAN and there aren't any open transactions 🙂

sqlmunkee SSCommitted Points: 1949 More actions · Answer 7

Can you track down the service that started the spids ?

If you stop the app or service that made the connection in the first place, that should kill the connection gracefully.

Had this recently - when we restarted some services on a remote calling server, the stalled spids went away.

[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]

bugg SSCertifiable Points: 7567 More actions · Answer 8

sqlmunkee (6/24/2014)
Can you track down the service that started the spids ?
If you stop the app or service that made the connection in the first place, that should kill the connection gracefully.
Had this recently - when we restarted some services on a remote calling server, the stalled spids went away.

Interesting, Its monitoring software from solar winds, I've stopped it pointing at the server but that didn't do anything maybe if I'll try close it down.

bugg SSCertifiable Points: 7567 More actions · Answer 9

Unfortunately stopping the monitoring software service didn't rectify the issue. Probably because i had already tried to kill the SPIDs. Is there anything i should be aware of

before restarting the sql server service. I am going to pause mirroring but is there anything else?

Thanks

bugg SSCertifiable Points: 7567 More actions · Answer 10

The service restart removed the stalled SPIDs , thanks for your help on this guys.