Home Forums SQL Server 2008 SQL Server 2008 Administration Can't Kill SPID “Transaction Rollback in Progress” RE: Can't Kill SPID “Transaction Rollback in Progress”

  • March 3, 2013 at 9:53 am

    #1592768

    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/