August 31, 2012 at 7:56 am
I have an uncommitted statement in perptual rollback mode in my database. When I try to kill the SPID associated with this statement, I get the "transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." error. This uncommitted statement is causing users to not be able to view the DB's table, view, and procedure trees. How do I stop this SPID?
August 31, 2012 at 8:15 am
guerillaunit (8/31/2012)
I have an uncommitted statement in perptual rollback mode in my database. When I try to kill the SPID associated with this statement, I get the "transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." error. This uncommitted statement is causing users to not be able to view the DB's table, view, and procedure trees. How do I stop this SPID?
I've never had a process I've killed report back an actual estimated time.
If it's rolling back, you'll just have to wait for it to finish.
If it is hung up while trying to roll back, then you'll need to restart the service (or possibly the server) to clear the process. This can happen for example if you use xp_cmdshell to run an executable that prompts for some interactive input. The process will appear to hang at that point as it is waiting for input, and when you kill it the rollback will hang as well as it is waiting for a response from that executable which is still waiting for input.
August 31, 2012 at 8:24 am
guerillaunit (8/31/2012)
I have an uncommitted statement in perptual rollback mode in my database. When I try to kill the SPID associated with this statement, I get the "transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." error. This uncommitted statement is causing users to not be able to view the DB's table, view, and procedure trees. How do I stop this SPID?
what's the reason for rollback? do you used kill spid? or occurs fails on server/database?
August 31, 2012 at 8:25 am
The rollback has been going on for a day now so I assume it is stuck. Any watchouts that need to be done when restarting the server? Also, how long does it usually take?
August 31, 2012 at 8:35 am
guerillaunit (8/31/2012)
The rollback has been going on for a day now so I assume it is stuck. Any watchouts that need to be done when restarting the server? Also, how long does it usually take?
Whenever I have seen this condition, I have never seen it complete on its own.
I suggest restarting SQL Server; its should not be necessary to reboot the server.
Depending the actual state of the rollback, it may take SQL Server longer than usual to recover the database.
August 31, 2012 at 8:40 am
Thanks Michael. I was hoping to avoid the restart, but it looks like there are no alternatives. I've never restarted a server before. Any things to watch out for other than to avoid doing it when the DBs are in high demand?
August 31, 2012 at 9:07 am
guerillaunit (8/31/2012)
Thanks Michael. I was hoping to avoid the restart, but it looks like there are no alternatives. I've never restarted a server before. Any things to watch out for other than to avoid doing it when the DBs are in high demand?
If the transaction is actually rolling back and not hung up, then you won't gain anything from restarting the server. When it comes back up, it will just continue rolling back the transaction.
I've had processes take as long to roll back as it had been running before being killed.
Can you tell us more about what the process is, how long it's been running, and why it is rolling back?
August 31, 2012 at 9:26 am
sestell1 (8/31/2012)
guerillaunit (8/31/2012)
Thanks Michael. I was hoping to avoid the restart, but it looks like there are no alternatives. I've never restarted a server before. Any things to watch out for other than to avoid doing it when the DBs are in high demand?If the transaction is actually rolling back and not hung up, then you won't gain anything from restarting the server. When it comes back up, it will just continue rolling back the transaction.
I've had processes take as long to roll back as it had been running before being killed.
Can you tell us more about what the process is, how long it's been running, and why it is rolling back?
The problem reported is a well known problem that Microsoft has failed to fix across multiple versions. I have seen it on SQL 7.0, 2000, 2005, 2008, and 2008 R2.
Maybe they will get around to it someday, but they don't seem to be in a hurry.
http://connect.microsoft.com/SQLServer/feedback/details/433703/killed-rollback
August 31, 2012 at 9:28 am
that's right! sestell1!!!
I ask you, again: what's the reason for rollback? do you used kill spid? or occurs fails on server/database?
depending of reasons, isn't appropriated restart server/sql!!!
August 31, 2012 at 9:43 am
It was a DB backup operation that took much longer than usual (1 hr vs. 5 + hrs.). I stopped the process and it has been in "Kill/Rollback" mode for half a day
August 31, 2012 at 10:17 am
guerillaunit (8/31/2012)
It was a DB backup operation that took much longer than usual (1 hr vs. 5 + hrs.). I stopped the process and it has been in "Kill/Rollback" mode for half a day
Ah, was the backup using compression?
If so, there is a bug that can cause a backup process to hang like that if it fails to complete successfully. The only way to clear it is to restart. I think MS released a hot-fix, but it didn't happen enough for me to risk applying an untested hot-fix.
EDIT - Here's a link to the hotfix:
September 3, 2012 at 7:52 pm
The restart fixed it. Gracias
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply