Siusic Dot Com

A coworker came to

me for help today when he tried to add two columns on to a very big table that contained 165 million records. He added these two columns to the table using SQL server management studio and after waiting for almost two hours he got query timeout message. So I looked at SQL server using activity monitor. You can see where Activity monitor resides in the following console tree

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:

How to Kill AutoShrink Process

Blog Income Estimation

SQL Server 2005 Profiler Duration

The Ultimate Strategy of Tuning Store Procedure Performance

A CommVault Galaxy QiNetix Bug Experienced in Restoring SQL Server Database