May 24, 2011 at 9:20 am
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?
May 24, 2011 at 9:23 am
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.
May 24, 2011 at 9:37 am
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
May 24, 2011 at 9:42 am
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/
May 24, 2011 at 10:46 am
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