KILLED/ROLLBACK thread in Activity Monitor

  • I have a session in the Activity Montior on a SQL 2008 machine that has KILLED/ROLLBACK in the command column. It's also the Head Blocker (= 1).

    This is stopping me from being able to access tables in my database. How can I speed up the removal of the session without rebooting the server?

  • Wait till it finishes. Depending upon what it was doing (did) it could take a while for the rollback to complete.

    The probability of survival is inversely proportional to the angle of arrival.

  • If you restart SQL, the rollback will continue after the restart and, depending on the edition the DB could be offline during the rollback.

    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
  • ah the joys of rollbacks and killed users!! sadly rollbacks seem to take so long - if you get all other users out of the DB it may help! I lost a whole prod system for most of an afternoon after someone managed to code that into a proc ( I didn't know until it happened!! )

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Is the query you killed running on a remote/linked server? If so, it's possible it's in a KILLED/ROLLBACK state on the server you killed it from, but still running a SELECT or something on the other server (and will appear stuck waiting for the remote query to complete what it's doing before the actual rollback takes place on the main server).

    Might not be your situation, but something to look for...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 5 posts - 1 through 4 (of 4 total)

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