May 21, 2011 at 4:55 am
I have a backup statement that I had to stop using the kill statement after 10 minutes. Now it's remaining in a killed/rollBack status. When I check status of the SPID, it says estimated rollback completion is 7% and it's been that way for 20 hours. The transaction log is growing because of this. Is there any way to stop this statement, short of restarting SQL, which would have the DB down forever to re-apply the logs, which are huge now.
I'm using SQL 2008 Enterprise Edition
Thanks
May 21, 2011 at 5:49 am
Wait.
It's rolling back and the rollback has to complete. If you restart SQL, the rollback will continue after the restart.
Check that it's not blocked, check there's nothing it's waiting for.
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 21, 2011 at 6:06 am
Thanks, I've been waiting for 20 hours. it's been at 7% practically the whole time and the estimated completion time has been increasing. There is nothing blocking.
May 21, 2011 at 6:40 am
You can wait, or you can restart and wait. There is no way to cancel or stop a rollback (if you could, it would result in the DB going suspect on the spot)
With enterprise edition you have fast recovery, so the rollback will continue after restart with the DB online (though roll forward portion of crash recovery is still offline)
Edit: just noticed it's a backup. Restart, every time I've seen that a restart has been required.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply