Estimated Rollback Completion: 0%. Estimated Time Remaining: 0 Seconds.
June 11th, 2008 by Andrew Chen
A coworker came to
It shows that the process adding the two columns was still running so I looked at the statements that was executed against the database. It was like
ALTER TABLE table_name ADD column_name VARCHAR(20) NOT NULL DEFAULT ‘DefaultValue’
I issued a kill statement to kill that process. The statement was like Kill 55. The process turned into Killed/Rollback status in activity monitor. And when I issue the same Kill statement again it showed the following information.
“Estimated Rollback Completion: 0%. Estimated Time Remaining: 0 Seconds.”
I issued the same kill statement again after two hours it still showed me the same message so I was wondering what was going on and I thought the rollback process must be hung. I search online for a solution and seems to me restart SQL service was the only way to clear it but that was a production server and I could not do that. I finally discover the best solution and that is to sit back and wait. Because the information report from the kill statement or “Kill spid statusonly” statement doesn’t report the correct information sometime. You simply can not trust what it said. If the process shows Killed/RollBack it must be in the Rollback process and you should feel safe to just wait on it. A lot of forum will tell you to restart SQL service but that doesn’t do any good. SQL server still have to continue the RollBack process after it is restarted.
The best approach is to make sure the Rollback process is not blocked by other process. Activity monitor will tell you that. When that is made sure then you basically need to sit back and wait for the Rollback to finish.
Related Posts:
(No Ratings Yet)
Loading ...
[…] against. When I kill the process that ran the store procedure I saw the same message, “Estimated Rollback Completion: 0%. Estimated Time Remaining: 0 Seconds“, which I talked about in my previous post. However no matter how long I wait this killed […]