How to stop XP_cmdshell (kill/Rollback)

Question

I observed that xp_cmdshell is in KILLED/ROLLBACK state in my SQL server and consuming memory from tempdb.

I tried to get the PID for this session id and the source server from which it was generated, I could not find the PID in source server tasklist.

Is there any way to stop this xp_cmdshell without restarting SQL server.

Answer 1

In windows, XP_CMDSHELL creates a new dependent CMD process on the machine where SQL Server is installed (not on the machine from where XP_CMDSHELL is triggered, It can be triggered from a remote machine using SSMS).

You will find a process with user name as MSSQLSERVER in task manager on that machine. You got to end this process as well.