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

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

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

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s