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

StackExchange Database Administrators Q#213076, answer score: 8