Jun 3, 2008

"KILLED/ROLLBACK" SQL Server Process Never Ends

You may see a killed SQL server process marked as "KILLED/ROLLBACK", but did not disappear from SQL Sevrer forever. Seems lots of people by doing restart the server or SQL service to clean up this issue. If your case is similar to mine, you may not need to do that again.

My Case:

A job was calling an external program, ex. winzip. It could not be finished as winzip registration file was missing.

After killing SQL process by issuing kill Command.

The process was marked as "KILLED/ROLLBACK", however it could not exit after a day.

Check the status: kill 463 with statusonly

SPID 463: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

My Workaround:

In this case, I looked at the process in Windows level, I saw a winzip.exe was still running. Of course first I isolated no winzip was using. I killed winzip OS process, the hung SQL process exited from SQL Server automatically.

My Environment:

SQL Server 2000 SE SP4
Windows Server 2003 R2