We come across a situation where we have to kill one spid which was running for quite some time, so we though killing will help us, but after killing found that that spid is not killed but stuck in our processes spid. And we could not able to kill even

“WITH STATUSONLY”

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

Means it is completed, what went wrong.

We could see that the spid is hang and we can even run dbcc inputbuffer() on that spid. After checking the processes we run on that spid we found that we have run some external command (xp) which is external and not controlled by sql server (windows process).

So what next, we have searched on internet but no luck. We found some interesting information.

We can check which kpid(windows process) is running by that spid using Sysprocesses (sql server 2000) and sys.sysprocesses(sql server 2005+) command.

Some experts says killing the kpid from process explorer(task manager) would help to solve this kind of problem.

http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/71d3cd0b-ce4d-4202-8e3b-38b38da90a5b

But on production you should never kill the kpid (Linchi Shea, Adam Machanic )

http://sqlblog.com/blogs/linchi_shea/archive/2010/02/04/killing-a-sql-server-thread-don-t.aspx

So the ultimate solution is let that process be hung. Try to make the changes in your code to avoid such situation.

The solution would be to restart the services.

“Never kill any KPID related to spid in production”

Reference:

http://sqlserver-qa.net/blogs/perftune/archive/2008/05/06/4120.aspx