Can't Kill SPID “Transaction Rollback in Progress”

  • I'm running into frequent Blocking on a Development Server.

    I have been correctly the code or making recommendations to prevent this but new code keeps getting executed and by the time I find out about it it is too late.

    When I attempt to kill the SPID the Rollback is stuck at 0%.

    I get the following message when I retry killing the SPID.

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

    KILL SPID WITH StatusOnly does not work.

    I have had to restart the SQL Server Service, Set the Database OFFLINE and try to bring it back ONLINE and and force it to recover.

    This is making me nervous.

    Any suggestions?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • There are no easy methods to kill the damned things. The 0% rollback is actually a documented problem and, IIRC, has an open/active CONNECT item against it.

    There are two types of 0% rollbacks... those that are benign and those that eat the processing time of nearly a full CPU. You can just ignore the ones that are NOT using CPU time. Tthe ones that are using scads of CPU are obviously a problem and, as of right now, the only ways to remove them is to either bounce the service or bounce the server.

    As a side bar, I try not to kill spids. I send the user an email. Then I find out where they sit and pay them a personal visit to try show them what they're doing wrong. I might even help them with their code. Most of the folks are absolutely grateful.

    --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)

  • Welsh Corgi (2/26/2013)


    I have had to restart the SQL Server Service, Set the Database OFFLINE and try to bring it back ONLINE and and force it to recover.

    Just a restart will do, no need for the rest.

    What's the query 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
  • The blocking that occurred happened yesterday was from a Statement that was Deleting from a Table using a Subquery.

    This query was placing Exclusive Table Locks.

    on two tables.

    The Transaction Log was growing and since it did not seem that the Database was Rolling Back, 0%, I made an assumption that it was stuck. Did I make an incorrect assumption?

    I had them using locking hints and no more blocking.

    After I went through restarting the Service and recovery.

    I set the Database Offline but when I attempted to bring it back online and I got an error, something to the effect that the database did not exist or I did not have permissions.

    I wonder if I had included with Rollback Immediate I could have avoided this problem?

    The Database was stuck offline. I checked the event viewer and I did not see any entries that indicated that the Database was in Recovery.

    So I bounced the Service again and shortly after I started seeing entries in the Event Log that the Database was recovering.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I have blocking going on between 2 different developers.

    The blocking has been going on for nearly an hour.

    I would have thought that there would have been a deadlock victim by now.

    How can I get out of this situation without a database recovery or worse yet a database restore?

    Edit: The machine is essentialy a production machine (long story) so it is critical.:crazy:

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (3/3/2013)


    I have blocking going on between 2 different developers.

    The blocking has been going on for nearly an hour.

    I would have thought that there would have been a deadlock victim by now.

    If it was a deadlock, SQL would have rolled one back. That it has not means that's not a deadlock. Just long duration blocking. Probably someone's left a transaction open and it's holding locks.

    Look at the blocking chains, find whoever's at the head of the blocking chain, ask them to stop their query or commit their transaction, whichever's appropriate. If they refuse, kill the session that's at the head of the blocking chain (it'll be the one that everything else is waiting for, that's not blocked itself.

    Do not restart SQL, that will result in the DB going into recovery.

    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
  • GilaMonster (3/3/2013)


    Welsh Corgi (3/3/2013)


    I have blocking going on between 2 different developers.

    The blocking has been going on for nearly an hour.

    I would have thought that there would have been a deadlock victim by now.

    If it was a deadlock, SQL would have rolled one back. That it has not means that's not a deadlock. Just long duration blocking. Probably someone's left a transaction open and it's holding locks.

    Look at the blocking chains, find whoever's at the head of the blocking chain, ask them to stop their query or commit their transaction, whichever's appropriate. If they refuse, kill the session that's at the head of the blocking chain (it'll be the one that everything else is waiting for, that's not blocked itself.

    Do not restart SQL, that will result in the DB going into recovery.

    Thanks Gail!

    I certainly could Google Blocking Chains but do you know of a good article? I know you are probably the author. 🙂

    One of the developes is at developers the other is not responding.

    But what about the problem when I kill the process it is stuck at 0% complete?

    How can I avoid that?

    Thanks.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (3/3/2013)


    I certainly could Google Blocking Chains but do you know of a good article? I know you are probably the author. 🙂

    Yes, you could google it. Right now I don't feel like pulling up google to search for you.

    But what about the problem when I kill the process it is stuck at 0% complete?

    How can I avoid that?

    Most times I've seen that it's been a process that has some external component. DTC, remote procedure call, external access, extended procedure, backup. Killing a normal SQL process you shouldn't have any problems. Just don't do anything silly like restarting SQL part way through a rollback or deleting the transaction log.

    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
  • The block went away.

    I did several searches and btw I did not expect you to search for me. I thought that you might have a favorite.

    I like the following.

    SELECT

    spid

    ,sp.STATUS

    ,loginame = SUBSTRING(loginame, 1, 12)

    ,hostname = SUBSTRING(hostname, 1, 12)

    ,blk = CONVERT(CHAR(3), blocked)

    ,open_tran

    ,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10)

    ,cmd

    ,waittype

    ,waittime

    ,last_batch

    ,SQLStatement =

    SUBSTRING

    (

    qt.text,

    er.statement_start_offset/2,

    (CASE WHEN er.statement_end_offset = -1

    THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2

    ELSE er.statement_end_offset

    END - er.statement_start_offset)/2

    )

    FROM master.dbo.sysprocesses sp

    LEFT JOIN sys.dm_exec_requests er

    ON er.session_id = sp.spid

    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

    WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

    AND blocked = 0

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • GilaMonster (3/3/2013)


    Welsh Corgi (3/3/2013)


    Most times I've seen that it's been a process that has some external component. DTC, remote procedure call, external access, extended procedure, backup. Killing a normal SQL process you shouldn't have any problems. Just don't do anything silly like restarting SQL part way through a rollback or deleting the transaction log.

    Yes but what do you do when the rollback does not make any process 0% of 0% completed?

    byw, The situation does not fall into any of the categories that you mentioned.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (3/3/2013)


    I like the following.

    sysprocesses is deprecated, will be removed in a future version and should not be used. Use sys.dm_exec_requests, sys.dm_exec_sessions and sys.dm_exec_connections instead.

    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
  • Welsh Corgi (3/3/2013)


    GilaMonster (3/3/2013)


    Welsh Corgi (3/3/2013)


    Most times I've seen that it's been a process that has some external component. DTC, remote procedure call, external access, extended procedure, backup. Killing a normal SQL process you shouldn't have any problems. Just don't do anything silly like restarting SQL part way through a rollback or deleting the transaction log.

    Yes but what do you do when the rollback does not make any process 0% of 0% completed?

    The vast majority times I've seen that, it's been one of the above cases and it's something outside of SQL that's 'stuck', there's no actual rollback to do and hence it can be ignored or SQL can be restarted, that's the 0%, 0 seconds remaining scenario.

    When you have 0% and a non-0 seconds, you wait for the rollback to finish, checking that it's not waiting for anything that you can fix.

    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
  • GilaMonster (3/3/2013)


    Welsh Corgi (3/3/2013)


    GilaMonster (3/3/2013)


    Welsh Corgi (3/3/2013)


    Most times I've seen that it's been a process that has some external component. DTC, remote procedure call, external access, extended procedure, backup. Killing a normal SQL process you shouldn't have any problems. Just don't do anything silly like restarting SQL part way through a rollback or deleting the transaction log.

    Yes but what do you do when the rollback does not make any process 0% of 0% completed?

    The vast majority times I've seen that, it's been one of the above cases and it's something outside of SQL that's 'stuck', there's no actual rollback to do and hence it can be ignored or SQL can be restarted, that's the 0%, 0 seconds remaining scenario.

    When you have 0% and a non-0 seconds, you wait for the rollback to finish, checking that it's not waiting for anything that you can fix.

    In this case it was a Stored Procedure being blocked by T-SQL Code.

    When I get the 0% there is nothing that I can do.

    I would expect that it would not be stuck at 0%?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (3/3/2013)


    GilaMonster (3/3/2013)


    Welsh Corgi (3/3/2013)


    GilaMonster (3/3/2013)


    Welsh Corgi (3/3/2013)


    Most times I've seen that it's been a process that has some external component. DTC, remote procedure call, external access, extended procedure, backup. Killing a normal SQL process you shouldn't have any problems. Just don't do anything silly like restarting SQL part way through a rollback or deleting the transaction log.

    Yes but what do you do when the rollback does not make any process 0% of 0% completed?

    The vast majority times I've seen that, it's been one of the above cases and it's something outside of SQL that's 'stuck', there's no actual rollback to do and hence it can be ignored or SQL can be restarted, that's the 0%, 0 seconds remaining scenario.

    When you have 0% and a non-0 seconds, you wait for the rollback to finish, checking that it's not waiting for anything that you can fix.

    In this case it was a Stored Procedure being blocked by T-SQL Code.

    When I get the 0% there is nothing that I can do.

    I would expect that it would not be stuck at 0%?

    If it's 0% complete and a non-0 seconds remaining, you wait. Rollbacks take longer than the time to make the original changes, so if you're rolling back something that took 3 hours, it'll take longer than 3 hours to roll back. Just check that the rollback is not blocked or waiting for something you can fix.

    I wouldn't kill the procedure in that case, I'd either kill the T-SQL that was causing the blocking or I'd get the person running it to stop the query (which amounts to the same thing)

    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
  • ok, thanks Gail.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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