Session in a limbo state - Killed/Rollback I have a SQLjob that calls xp_cmdshell that is used to dump a txt file to an ftp site via a script.

At times it fails and the job gets hung up in an executable state. Generally I would kill the session and it gets cleared.

However, I have two sessions that are hung in a limbo state.

 sp_WHO2 53 -- running from 7/8/2018




SP_WHO2 98 -- running from 6/16/2018




The job status says its Runnable but the Command is KILLED/ROLLBACK.

Checking SPID 53

Kill 53 with status only 


Output


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



Checking SPID 98

KILL 98 with status only


Output


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



How do I go about clearing these sessions?

I am having performance issues with the SQL Server, which led me to these sessions.
I did some research and points me to believe that it will increase the log files and cause performance issues. I am unaware if the current issue is because of these sessions but they are active expensive queries. So I thought of starting from here. I am developer here and I am aware that activity monitor is obsolete in SQL SERVER 2014 But i did check activity monitor and below are the images.





Can you suggest how I can go about it? 

I did read a blog about such a scenario by Brent Ozar
- Rollback: What happens when you KILL a session?. 

Learnt the hard way, looking into fixing the root cause.

AK
You could try to kill the windows process that was spawned with xp_cmdshell as that process is probably running and not handled by killing the spid from SQL Server. The way you describe it I would guess the ftp program is still running.

This is because the kill command doesn't kill programs that were started out of process.

You could simulate the scenario like this:

In a SQL Query window start a process:

xp_cmdshell 'notepad.exe'


and then after killing that spid I get the same output in sp_who2 as you

54      RUNNABLE                        Administrator   hostname      . master  KILLED/ROLLBACK     0   0   07/24 14:23:02  Microsoft SQL Server Management Studio - Query  54      0  


But I still have a running notepad.exe process:

C:\Users\Administrator&gt;tasklist | find "notepad"
notepad.exe                   4676 Services                   0      3 788 K


Once I kill that notepad.exe process my SQL Server spid is gone too.

If you want to find out what process you need to kill process explorer should show it as a child process of sqlserver.exe like this (you might need to start procexp.exe as administrator):