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
Checking SPID 53
Kill 53 with status only
SPID 53: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
Checking SPID 98
KILL 98 with status only
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.