4

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 53

SP_WHO2 98 -- running from 6/16/2018

SP_WHO2 98

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.

Image from activity monitor - 1

Image from activity monitor - 2

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

8

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.