What you don't know is that whatever xp_CmdShell was running, is still running! Restarting the service clears the link to SQL Server but the process in Windows is still running. Depending on what it's doing, it could still be consuming a shedload of CPU, as well.

There are only two valid ways to kill a stuck xp_CmdShell call. One is to bounce the whole server, not just the SQL Service. Obviously, that's even worse than just bouncing the service.

The other way (which is the best way) is to kill the PROCESS that's running including any and all subprocesses that may be attached to the main process. While not horribly difficult to do manually if you know how, the DBA might not have OS level access to the SQL Server and, if there are a whole lot of Cmd Shell processes running, it can be a bit daunting to find just the one you need to kill. Obviously, you'll need to know what the issued command was to make a really good decision there.

With all of that in mind, the following stored procedure will help you find all of this and successfully kill the offending PID and the related subtree. Rather than regurgitate how to use it, all of that is in the documentation in the code. In fact, if you run the stored procedure without any parameters or with 'Jelp', it will display the help as a return from the stored procedure. It is setup to ONLY find Cmd.EXE with the "/C" switch but that doesn't mean it will only find PIDs started by xp_CmdShell. ANY Cmd.EXE instance with the /C switch will appear. Since most people don't run a DOS Window session using the /C switch, it will likely not find those (you don't need to kill those, usually). Still, you're killing a PID and you need to pay attention to what you're killing.

And, yes... I install this on the Master database of all my servers so I don't have to look for it if there is such an emergency. READ AND UNDERSTAND ALL THE COMMENTS BEFORE YOU USE IT OR DON'T USE IT! It's not dangerous code but how you use it might be. 😉

The best part about this code is that you're no longer required to bounce the server or the service to kill a stuck xp_CmdShell SPID!

Here's the code.

CREATE PROCEDURE [dbo].[sp_FindCmdProcess]

/**********************************************************************************************************************

Purpose:

Finds and kills processes started by xp_CmdShell so that "frozen" SPIDs can be killed.

All related child processes will also be killed.

Please see the "Help" section of the code or simply run this proc with no parameters for syntax and other information.

If you want to make this sproc available from any database, create this stored procedure in the Master database and

then run the following code.

--===== Make the listed sproc a "system object".

USE MASTER;

EXEC sp_ms_marksystemobject 'sp_FindCmdProcess'

;

--===== Verify that that mark "took".

SELECT name, is_ms_shipped

FROM sys.objects

WHERE name = 'sp_FindCmdProcess'

;

Revision History:

Rev 00 - 23 Feb 2014 - Jeff Moden - Initial release and unit testing.

**********************************************************************************************************************/

--===== Declare the I/O parameters for this sproc.

@pLookFor VARCHAR(8000) = 'Help'

,@pAction CHAR(4) = 'List'

AS

--=====================================================================================================================

-- Presets

--=====================================================================================================================

--===== Environmental Settings

SET NOCOUNT ON;

--===== Temp Table(s)

IF OBJECT_ID('tempdb..#CmdResponse','U') IS NOT NULL

DROP TABLE #CmdResponse

;

CREATE TABLE #CmdResponse

(

Line# INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

,CmdOutput VARCHAR(8000)

)

;

--===== Local variables

DECLARE @Cmd VARCHAR(8000)

,@CmdKill VARCHAR(MAX)

,@ProcessIdStart INT

,@LookFor VARCHAR(8000)

,@Action VARCHAR(10)

;

--=====================================================================================================================

-- Validate and Delouse Inputs

--=====================================================================================================================

SELECT @LookFor = @pLookFor

,@Action = @pAction

WHERE @pLookFor LIKE '%[^&|#]%' --Reject DOS injection by disallowing embedded/multiple commands and comments

AND @pLookFor NOT LIKE 'REM %'--Reject DOS injection by disallowing REM statments

AND @pAction IN ('Help','List','Kill')

;

IF @LookFor IS NULL

OR @Action IS NULL

BEGIN

RAISERROR('No Action Taken.',16,1);

RETURN 1;

END

;

--=====================================================================================================================

-- First, check to see if someone is simply looking for help with syntax

--=====================================================================================================================

IF @LookFor = 'Help'

BEGIN

PRINT '

***Help on sp_FindCmdProcess***

Rev 00 - 23 Feb 2014 - Jeff Moden - Initial release and unit testing.

Purpose:

Finds and kills select processes started by xp_CmdShell so that "frozen" SPIDs can be killed.

More specifically, it only finds instances of cmd.exe /c and will not find instance of just

cmd.exe for safety sake.

All related child processes will also be killed.

Syntax:

EXEC sp_FindCmdProcess @pLookFor, @pAction;

@pLookFor can be...

If not present, will default to ''HELP''.

If blank or NULL, will error out.

''HELP'' -- Overrides everything and returns this help listing.

''somestring'' -- Find all cmd.exe /c processes that contain this ''somestring''.

''All'' -- Find all cmd.exe /c processes.

somenumber -- Find the process that has a ProcessID = somenumber.

''somenumber'' -- Find the process that has a ProcessID = somenumber.

May not contain the special symbols of &, |, or #.

May not contain values that start with REM

May contain the % wild card that is used by WMIC.

@pAction can be...

If not present, will default to ''List''.

If blank or NULL, will error out.

''List'' -- Will list all found cmd.exe /c processes with no action taken.

''Kill'' -- Will KILL all found cmd.exe /c processes after a 20 second

"stopable" delay period. Click the STOP button during the

delay period to abort the run with no action taken. Once the

kills begin, the code will run to completion killing all

found processes according to the @pLookFor parameter.

This stored procedure will NOT list or kill cmd.exe processes that are not using

the /c sub-processor. This means that it will not list or kill "Command" windows

that have been opened by users unless they manually invoked the /c sub-processor

at the time they invoked cmd.exe.

'

RETURN 0 ;

END

;

--=====================================================================================================================

-- Find the cmd.exe tasks that we're looking for.

--=====================================================================================================================

--===== Create the necessary dynamic DOS command to find the processes that we're looking for.

SELECT @Cmd = CASE --Determine what to find

WHEN @LookFor = 'ALL'

THEN 'WMIC PROCESS WHERE (Name="cmd.exe" AND CommandLine LIKE "%/c %") GET CommandLine,ProcessId'

WHEN @LookFor NOT LIKE '%[^0-9]%' --@LookFor is all digits for a ProcessID

THEN REPLACE(

'WMIC PROCESS WHERE (Name="cmd.exe" AND ProcessID="<<@LookFor>>") GET CommandLine,ProcessId'

,'<<@LookFor>>',@LookFor

)

ELSE REPLACE(

'WMIC PROCESS WHERE (Name="cmd.exe" AND CommandLine LIKE "%/c <<@LookFor>>") '

+'GET CommandLine,ProcessId'

,'<<@LookFor>>',@LookFor

)

END

;

--===== Store the processes we're looking for in a working table.

INSERT INTO #CmdResponse

(CmdOutput)

EXEC xp_CmdShell @Cmd

;

--=====================================================================================================================

-- Desired @Action = 'List'

-- Just list everything we found and exit gracefully.

--=====================================================================================================================

--===== List all of the processes we found no matter what the desired action is.

SELECT *

FROM #CmdResponse

WHERE CmdOutput > ''

AND CmdOutput NOT LIKE '%/c WMIC PROCESS WHERE (Name="cmd.exe"%GET CommandLine,ProcessId%'

ORDER BY Line#

;

--===== If the desired action is simply to list the processes we found, exit now.

IF @Action = 'List' RETURN 0

;

--=====================================================================================================================

-- Desired @Action = 'Kill' (assumed after fallthrough if 'List' wasn't the action from above.

-- Kill all processes that were found.

--=====================================================================================================================

--===== Find where the floating ProcessId column starts so that we can isolate the ProcessIds to kill.

SELECT @ProcessIdStart = CHARINDEX('ProcessId',CmdOutput)

FROM #CmdResponse

WHERE Line# = 1

;

--===== Build the dynamic DOS statements we need to kill the found processes.

-- /f = Force termination

-- /t = Terminate child processes, as well.

SELECT @CmdKill = ISNULL(@CmdKill,'')

+ REPLACE('EXEC xp_CmdShell ''taskkill /f /t /fi "pid eq <<ProcessID>>"'''+CHAR(10)

, '<<ProcessID>>'

, RTRIM(REPLACE(REPLACE(SUBSTRING(CmdOutput,@ProcessIdStart,8000),CHAR(10),''),CHAR(13),''))

)

FROM #CmdResponse

WHERE CmdOutput NOT LIKE '%/c WMIC PROCESS WHERE (Name="cmd.exe"%GET CommandLine,ProcessId%'

AND CmdOutput > ''

AND Line# > 1

;

--===== Warn the operator of what is about to happen.

SELECT [READ ME NOW!!! ] =

'*** WARNING *** WARNING *** WARNING *** WARNING *** WARNING *** WARNING ***' UNION ALL

SELECT ' YOU ARE ABOUT TO KILL ALL OF THE PROCESSES LISTED ABOVE.' UNION ALL

SELECT ' YOU HAVE 20 SECONDS TO ABORT BY CLICKING THE STOP BUTTON.' UNION ALL

SELECT '*** WARNING *** WARNING *** WARNING *** WARNING *** WARNING *** WARNING ***'

;

RAISERROR('READ THE GRID NOW IF IT''S ACTIVE!!!',0,1) WITH NOWAIT;

--===== Now, give the operator some time to read and react to the warning

WAITFOR DELAY '00:00:20'

--===== TIME'S UP! Waste 'em!

EXEC (@CmdKill)

;