April 7, 2008 at 7:53 am
I get this message,
SPID 139: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
I had to reboot the server to kill the process, I want to avoid that,
Please Assist,
Thanks,
Dev
April 7, 2008 at 8:23 am
The spid was performing a rollback - probably because it had already been killed, but not necessarily so.
You cannot stop a rollback that is incomplete - it would leave you database in an inconsistent state. You need to let the spid complete the rollback. When you restarted, the rollback was completed when the database came back online.
April 8, 2008 at 3:53 am
Yeah, that's true, killing off a rollback that's incomplete will leave your data inconsistent (That's bad by the way) but 0% complete 0 seconds remaining smacks of a hang, especially if it's been like that for several hours.. I guess the OP was looking for a way to kick the server back into a working state..
The only other way out of this that I've found is to restart the SQL services relating to that instance.. It is essentially the same as rebooting the server but if your system is running other things (like a Small Business Server for example) then you won't have to kick your users off.. Be VERY sure that your server has hung before you take this action.. DBCC should sort it out but you could end up with an unusable database..
April 8, 2008 at 4:16 am
[font="Courier New"]Hi,
You can use the below query to check the rollback status.
Kill 139 with statusonly[/font]
Regards..Vidhya Sagar
SQL-Articles
April 8, 2008 at 7:39 am
Whatever you do, please be sure that you do not restart the server while the transaction is in the rollback state...this might lead to database going into 'SUSPECT'. Then you will need to go for restoring the database.
Ambuj
April 8, 2008 at 7:44 am
hello,
The Rollback just stays at 0% and does not move at all, I beleive I am left with no option,
Please Advice,
Thanks,
Dev
April 8, 2008 at 8:13 am
Is the Rollback process being blocked by any other process? Please check that you rollback operation is not blocked before you make move.
April 8, 2008 at 8:49 am
Ambuj Mathur (4/8/2008)
Whatever you do, please be sure that you do not restart the server while the transaction is in the rollback state...this might lead to database going into 'SUSPECT'. Then you will need to go for restoring the database.Ambuj
This isn't correct.
If that were the case, we'd have suspect db's all over the place.
What you will loose though, is the time already spent on the rollback, since that will start all over again during the recovery process when the server is started again.
As for spids that are dead/zombies regardless of their displayed status, a stop/start of the server is usually the only way to clear it up.
Obviously you want to make certain that it's actually dead before resorting to drastic measures.
/Kenneth
April 8, 2008 at 9:00 am
/Kenneth is right on.
I just encountered the same issue recently and the only thing that corrected it was the services restart.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply