spid in Killed/Rollback cannot kill – external

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


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).

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.


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


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”



This entry was posted in Add/remove, Lock/Blocking, Others and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.