Post reply

SQL Server RUNNABLE SPID - KILLED/ROLLBACK

  • 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

  • June 23, 2014 at 2:34 am

    #1723928

    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

  • June 23, 2014 at 2:58 am

    #1723938

    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?

  • June 23, 2014 at 3:04 am

    #1723942

    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

  • June 23, 2014 at 3:19 am

    #1723950

    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.

  • June 23, 2014 at 3:37 am

    #1723959

    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

  • June 23, 2014 at 3:48 am

    #1723971

    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 🙂

  • June 24, 2014 at 6:00 am

    #1724295

    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]

  • June 24, 2014 at 6:46 am

    #1724311

    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.

  • June 25, 2014 at 12:56 am

    #1724525

    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

  • June 25, 2014 at 1:47 am

    #1724532

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

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

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