October 25, 2010 at 2:55 am
Hi,
We have an issue where a SPID is locking a table but when I run kill 51 the SPID does not clear. if I run KILL 51 with statusonly i get the following message.
SPID 51: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
I know most people will say wait as the rollback can take some time but the sql query has been rolling back since Thursday morning. So i belive there must be another issue because 4 days to roll back is not right.
From what i've read the only solution seems to be restart the sql service but i do not want to do this if at all possible.
So can any suggest ways I can clear this SPID?
Thanks,
Russell
October 25, 2010 at 3:17 am
October 25, 2010 at 3:38 am
Dugi (10/25/2010)
Try to check sys.dm_exec_requests !?
Yep, the process appears but not sure what the next steps should be.
Any advice?
October 25, 2010 at 3:39 am
russell.keeney (10/25/2010)
Dugi (10/25/2010)
Try to check sys.dm_exec_requests !?Yep, the process appears but not sure what the next steps should be.
Any advice?
Sorry I should have said that the blocking_session_id column is 0
October 25, 2010 at 4:39 am
Wait or restart. There are no other options.
If you restart, the rollback will continue during crash recovery. During this time the database will not be available (unless you have enterprise edition). It may clear quickly, it may take quite some time.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply