November 4, 2015 at 7:07 am
I have SQL job that run a stored procedure. The procedure has a call to xp_cmdshell and the application hangs every once in a while. I have two questions.
1. How do I find the spid of the appropriate xp_cmdshell? sys.dm_exec_sql_text only gives me "xp_cmdshell". Since I have multiple xp_cmdshell running concurrently, I need to be able to kill the correct xp_cmdshell. For that, I need to know the command-line application called by xp_cmdshell.
2. Assuming I can find the appropriate command-line app that xp_cmdshell , is there a way to kill the command-line application?
November 4, 2015 at 8:37 am
Yes there is. I'll post it tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2015 at 9:23 am
You can find the SPID (SQL session ID) of the request using this script:
USE master;
SELECT
r.session_id
,db_name(database_id) db
,r.status
,r.blocking_session_id blockedBy
,r.start_time
,r.wait_type
,r.total_elapsed_time/1000 secs
,command = SUBSTRING (txt.text, r.statement_start_offset/2,
((CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), txt.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2)+1)
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) as txt
WHERE txt.text LIKE '%xp_cmdshell%' AND r.session_id != @@SPID;
However, xp_cmdshell launches cmd.exe so it's the Windows PID that you need to find to be able to kill the process. You can find the PID by using SysInternals' Process Explorer. Look for cmd.exe processes running under the account of your SQL Server service account, or your xp_cmdshell proxy account, if it's a non-sysadmin user executing the job. Double-click on the cmd.exe process and you will see the command that was executed in the Command Line field. If it matches with your stored procedure name, you can kill the process from the same window with the Kill Process button.
November 4, 2015 at 10:02 am
SpeedySQL (11/4/2015)
You can find the SPID (SQL session ID) of the request using this script:
USE master;
SELECT
r.session_id
,db_name(database_id) db
,r.status
,r.blocking_session_id blockedBy
,r.start_time
,r.wait_type
,r.total_elapsed_time/1000 secs
,command = SUBSTRING (txt.text, r.statement_start_offset/2,
((CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), txt.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2)+1)
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) as txt
WHERE txt.text LIKE '%xp_cmdshell%' AND r.session_id != @@SPID;
I was aware of these commands, but this doesn't suffice. I need to know the windows command called by xp_cmdshell so I kill only the appropriate windows application. As you provided, I would kill all apps called by multiple xp_cmdshell commands.
November 5, 2015 at 7:48 am
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)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply