如何防止从 xp_cmdshell 运行的进程永远处于 KILLED/ROLLBACK 状态? [英] How to protect against a process run from xp_cmdshell remaining in KILLED/ROLLBACK state forever?
本文介绍了如何防止从 xp_cmdshell 运行的进程永远处于 KILLED/ROLLBACK 状态?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在 SQL Server 2016 SP1(即将升级到更新版本)上使用 xp_cmdshell 来运行一些东西，主要是一个调用 psftp.exe 的批处理文件，用于通过 SFTP 发送文件.
非常偶尔，psftp.exe 会在发送文件的过程中被杀死.或者，该过程将在发送过程中崩溃.无论哪种方式，这意味着使用 xp_cmdshell 生成 psftp.exe 的 SPID 将永远处于 KILLED/ROLLBACK 状态，因为数据库正在等待通过命令 shell 来自批处理文件的某些响应，而该响应永远不会到达.
您可以像这样自己测试:
在 SQL 查询窗口中启动 notepad.exe 进程
xp_cmdshell 'notepad.exe'
然后KILL你刚刚创建的spid并检查sp_who2的输出:
54 RUNNABLE 管理员主机名.master KILLED/ROLLBACK 0 0 07/24 14:23:02 Microsoft SQL Server Management Studio - 查询 54 0即使没有发生回滚，这也会永远保持这种状态.您仍将有一个正在运行的 notepad.exe 进程:
C:\Users\Administrator>tasklist |找到记事本"notepad.exe 4676 服务 0 3 788 K一旦您退出/结束该 notepad.exe 进程，SQL Server spid 也将消失.
这可能会阻塞另一个使用与卡住进程相同的 DB 的进程，反过来又可能阻塞 TEMPDB，这非常糟糕.我找到的唯一解决方案是远程进入服务器并使用任务管理器终止卡住的进程，从而解除所有阻塞.
我有几个问题:
1:我可以阻止这种行为吗?是否有某种进程隔离或任务隔离可以在不绑定到 SPID 的情况下运行批处理文件?例如，我是否可以使用一些无法阻止数据库的封闭进程启动我的批处理文件，因此不会陷入回滚状态，或者如果确实如此，它也不会阻止其他任何事情?
2:我可以运行一个脚本来检测这种情况并自动修复它吗?例如我可以每 5-10 分钟检查一次 xp_cmdshell 进程是否卡在 KILLED\ROLLBACK 中，找出保持该状态的 Windows 任务并停止它吗?
3:这些问题是否在更新的 SQL Server 版本中得到解决?更新到 2019 年是否可以轻松解决问题?
欢迎任何建议(除了不要这样使用你的 SQL Server，笨蛋！").
解决方案
多亏了@Jeroen Mostert 的一些建议，我才能够想出以下解决方案.这是我从代理作业定期运行的一个存储过程，它检查并修复由于终止正在运行 xp_cmdshell 任务的会话而导致的卡在 KILLED/ROLLBACK 中的会话:
创建程序 [dbo].[sp_KillStuckSessions]作为开始设置无计数；声明 @ErrorText varchar(4000)--找出我们是否有任何会话卡在 KILLED/ROLLBACK 状态:如果不存在(选择 a.spidFROM sys.sysprocesses 一个 WITH(NOLOCK)INNER JOIN sys.dm_exec_sessions b WITH(NOLOCK) ON a.spid = b.session_idWHERE a.cmd = 'KILLED/ROLLBACK'AND DATEDIFF(minute,a.last_batch,GETDATE()) >5)--如果我们不这样做，请跳到最后，什么都不做.转到端点--找出卡住的会话实际上是什么声明 @StuckTaskName varchar(1000)声明 @NumStuckJobs 整数SELECT @NumStuckJobs = COUNT(*) FROM (选择 (SELECT text FROM sys.dm_exec_sql_text(a.sql_handle)) AS SqlCommandFROM sys.sysprocesses 一个 WITH(NOLOCK)INNER JOIN sys.dm_exec_sessions b WITH(NOLOCK) ON a.spid = b.session_idWHERE a.cmd = 'KILLED/ROLLBACK'AND DATEDIFF(minute,a.last_batch,GETDATE()) >5) 一种--如果它是单个xp_cmdshell进程，我们想杀死它如果@NumStuckJobs <>1开始SET @ErrorText = '超过 1 个会话卡在 KILLED/ROLLBACK - KillStuckSessions 不能杀死超过 1 个.请远程进入服务器并修复.'转到错误处理结尾别的SET @StuckTaskName = (SELECT (SELECT text FROM sys.dm_exec_sql_text(a.sql_handle)) AS SqlCommandFROM sys.sysprocesses 一个 WITH(NOLOCK)INNER JOIN sys.dm_exec_sessions b WITH(NOLOCK) ON a.spid = b.session_idWHERE a.cmd = 'KILLED/ROLLBACK')--如果还有别的，我们想用自定义错误文本引发错误消息.如果@StuckTaskName <>'xp_cmdshell'开始SET @ErrorText = '卡住的会话是由 xp_cmdshell 以外的其他原因引起的 - KillStuckSessions 无法终止此进程.请远程进入服务器并修复.转到错误处理结尾-- 如果我们走到这一步，我们知道有一个卡住的会话，它是一个 xp_cmdshell 任务.--让我们杀死任务吧！--声明一堆变量，包括一个表变量来存储我们的 wmic 命令的输出声明 @wmicdata 表 (ProcessID varchar(1000) NULL)声明 @SQLServerPID 整数声明 @PIDTestA1 整数声明 @PIDTestA2 整数声明 @PIDTestB1 整数声明 @PIDTestB2 整数声明 @OrphanedPID 整数声明 @StuckProcess1 整数声明 @StuckProcess2 整数--首先，我们运行它来获取 sqlserver.exe 的 PID，这将产生 xp_cmdshell插入@wmicdataEXEC xp_cmdshell 'wmic process where (name="sqlservr.exe") get ProcessID'SET @SQLServerPID = (SELECT CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9]%'), ProcessID), 8000) + 'X') -1) AS 整数)来自@wmicdataWHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') 不是 NULL并且 ProcessID 不为空)--接下来，我们运行两次以获取sql server任何卡住的子进程的进程ID.声明@cmdshellInput varchar(1000)SET @cmdshellInput = 'wmic process where (ParentProcessId=' + CAST(@SQLServerPID AS varchar(10)) + ') get ProcessID'--运行1删除@wmicdata插入@wmicdataEXEC xp_cmdshell @cmdshellInput设置@PIDTestA1 = (SELECT TOP 1 CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9])%', ProcessID), 8000) + 'X') -1) AS 整数)来自@wmicdataWHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') 不是 NULL并且 ProcessID 不为空ORDER BY ProcessID ASC)设置@PIDTestA2 = (SELECT TOP 1 CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9])%', ProcessID), 8000) + 'X') -1) AS 整数)来自@wmicdataWHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') 不是 NULL并且 ProcessID 不为空ORDER BY ProcessID DESC)- 等一等等待延迟 '00:00:01'--运行2删除@wmicdata插入@wmicdataEXEC xp_cmdshell @cmdshellInput设置@PIDTestB1 = (SELECT TOP 1 CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9])%', ProcessID), 8000) + 'X') -1) AS 整数)来自@wmicdataWHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') 不是 NULL并且 ProcessID 不为空ORDER BY ProcessID ASC)设置@PIDTestB2 = (SELECT TOP 1 CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9])%', ProcessID), 8000) + 'X') -1) AS 整数)来自@wmicdataWHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') 不是 NULL并且 ProcessID 不为空ORDER BY ProcessID DESC)-- 不会立即改变的数字是我们孤立的 cmd.exe 进程.-- 确实改变的数字是我们正在运行以获取 PID 的 wmic 命令！如果@PIDTestA1 = @PIDTestB1SET @OrphanedPID = @PIDTestA1如果@PIDTestA1 = @PIDTestB2SET @OrphanedPID = @PIDTestA1如果@PIDTestA2 = @PIDTestB1SET @OrphanedPID = @PIDTestA2如果@PIDTestA2 = @PIDTestB2SET @OrphanedPID = @PIDTestA2--如果它们都不匹配，那么我们就有问题了.如果@OrphanedPID 为空开始SET @ErrorText = '卡住的会话是由 xp_cmdshell 以外的东西引起的 - KillStuckSessions 不能终止这个进程.请远程进入服务器并修复.转到错误处理结尾--接下来，在orhpaned的cmd.exe进程上再次运行，找出我们要杀掉的子进程-- 总共应该有 2 个我们想要杀死的任务，一个 conhost.exe 和我们从 xp_cmdshell (psftp.exe) 调用的实际可执行文件SET @cmdshellInput = 'wmic process where (ParentProcessId=' + CAST(@OrphanedPID AS varchar(10)) + ') get ProcessID'删除@wmicdata插入@wmicdataEXEC xp_cmdshell @cmdshellInput--识别卡住的进程 1设置@StuckProcess1 = (SELECT TOP 1 CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9])%', ProcessID), 8000) + 'X') -1) AS 整数)来自@wmicdataWHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') 不是 NULL并且 ProcessID 不为空ORDER BY ProcessID ASC)--识别卡住的进程 2设置@StuckProcess2 = (SELECT TOP 1 CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9])%', ProcessID), 8000) + 'X') -1) AS 整数)来自@wmicdataWHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') 不是 NULL并且 ProcessID 不为空ORDER BY ProcessID DESC)--获取我们将要杀死的进程的 .exe 名称声明 @exe1 为 varchar(1000)声明 @exe2 为 varchar(1000)SET @cmdshellInput = 'wmic process where (ParentProcessId=' + CAST(@OrphanedPID AS varchar(10)) + ') get Name'删除@wmicdata插入@wmicdataEXEC xp_cmdshell @cmdshellInputSET @exe1 = (SELECT TOP 1 ProcessID FROM @wmicdata WHERE ProcessID LIKE '%.%' ORDER BY ProcessID ASC)SET @exe2 = (SELECT TOP 1 ProcessID FROM @wmicdata WHERE ProcessID LIKE '%.%' ORDER BY ProcessID DESC)--最后，运行这些来杀死孤立的子任务SET @cmdshellInput = 'taskkill/f/pid' + CAST(@StuckProcess1 AS varchar(10))EXEC xp_cmdshell @cmdshellInputSET @cmdshellInput = 'taskkill/f/pid' + CAST(@StuckProcess2 AS varchar(10))EXEC xp_cmdshell @cmdshellInput-- 一个进程被杀死的电子邮件声明@EmailBody varchar(4000)SET @EmailBody = '导致 SQL 会话卡在 KILLED/ROLLBACK 中的任务已在服务器上自动终止.<P>被终止的可执行文件的名称:<P>'+ @exe1 + '<P>'+ @exe2EXEC Automation.dbo.sp_SendEmailExternally --这是我的另一个发送电子邮件的存储过程.你可以把它换成 sp_send_dbmail@Recipients = '你的@email.here',@Subject = 'Agent Job KillStuckSessions 发现并终止了一个卡住的会话！',@HTMLBody = @EmailBody- 完成的！现在终止/回滚会话将消失.--跳过错误处理部分转到端点错误处理:--由于我们从代理作业运行，因此在此处使用 RAISERROR 将生成警报电子邮件，代理作业历史记录将包含自定义 @ErrorText 消息.RAISERROR (@ErrorText, 16, 1)终点:结尾走I'm using xp_cmdshell on SQL Server 2016 SP1 (soon to upgrade to a newer version) to run a couple of things, mainly a batch file that calls psftp.exe, for sending files over SFTP.
Very occasionally, psftp.exe will be in the process of sending a file and the process will be killed. Alternatively, the process will crash mid-send. Either way, this means the SPID that spawned psftp.exe with xp_cmdshell stays in a KILLED/ROLLBACK state forever, since the Database is waiting for some response from the batch file via the command shell, which never arrives.
You can test this yourself like so:
In a SQL Query window start a notepad.exe process
xp_cmdshell 'notepad.exe'
then KILL the spid you just created and check the output of sp_who2:
54 RUNNABLE Administrator hostname . master KILLED/ROLLBACK 0 0 07/24 14:23:02 Microsoft SQL Server Management Studio - Query 54 0
This will stay like this forever even though no Rollback is happening. You will still have a running notepad.exe process:
C:\Users\Administrator>tasklist | find "notepad"
notepad.exe 4676 Services 0 3 788 K
Once you quit / end that notepad.exe process, the SQL Server spid is gone too.
It's possible for this to block another process that's using the same DB as the stuck process, and in turn it's possible for that to block TEMPDB, which is very bad. The only solution I've found is to remote into the server and use Task Manager to kill the stuck process, which unblocks everything.
I have a few questions:
1: Can I prevent this behaviour? Is there some kind of process isolation or task isolation I can do to run the batch file without it being bound to a SPID? as in, could I launch my batch file using some boxed-off process that can't block the DB, and therefore can't get stuck in a rollback state, or if it does, it doesn't block anything else?
2: Can I have a script running to detect this situation and automatically remedy it? e.g. Could I check every 5-10 minutes for a xp_cmdshell process stuck in KILLED\ROLLBACK, figure out the Windows task that's keeping in that state and stop it?
3: Are these issues addressed in a more recent SQL Server edition? Would updating to 2019 solve the issue with minimal effort?
Any advice (other than "Don't use your SQL Server this way, dumbass!") welcomed.
解决方案
Thanks to some pointers from @Jeroen Mostert I was able to come up with the following solution. It's a stored procedure that I run periodically from an Agent Job, which checks for and fixes sessions stuck in KILLED/ROLLBACK that were caused by killing a session that was in the middle of running an xp_cmdshell task:
CREATE PROCEDURE [dbo].[sp_KillStuckSessions]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrorText varchar(4000)
--Find out if we have any Sessions stuck in a KILLED/ROLLBACK state:
IF NOT EXISTS ( SELECT a.spid
FROM sys.sysprocesses a WITH(NOLOCK)
INNER JOIN sys.dm_exec_sessions b WITH(NOLOCK) ON a.spid = b.session_id
WHERE a.cmd = 'KILLED/ROLLBACK'
AND DATEDIFF(minute,a.last_batch,GETDATE()) > 5)
--If we don't, skip to the end and do nothing.
GOTO Endpoint
--Find out what the stuck session actually is
DECLARE @StuckTaskName varchar(1000)
DECLARE @NumStuckJobs integer
SELECT @NumStuckJobs = COUNT(*) FROM (
SELECT (
SELECT text FROM sys.dm_exec_sql_text(a.sql_handle)) AS SqlCommand
FROM sys.sysprocesses a WITH(NOLOCK)
INNER JOIN sys.dm_exec_sessions b WITH(NOLOCK) ON a.spid = b.session_id
WHERE a.cmd = 'KILLED/ROLLBACK'
AND DATEDIFF(minute,a.last_batch,GETDATE()) > 5
) a
--If its a single xp_cmdshell process, we want to kill it
IF @NumStuckJobs <> 1
BEGIN
SET @ErrorText = 'More than 1 session is stuck in KILLED/ROLLBACK - KillStuckSessions cannot kill more than 1. Please remote into the server and fix.'
GOTO ErrorHandling
END
ELSE
SET @StuckTaskName = (SELECT (SELECT text FROM sys.dm_exec_sql_text(a.sql_handle)) AS SqlCommand
FROM sys.sysprocesses a WITH(NOLOCK)
INNER JOIN sys.dm_exec_sessions b WITH(NOLOCK) ON a.spid = b.session_id
WHERE a.cmd = 'KILLED/ROLLBACK')
--If its anything else, we want to raise an error message with custom error text.
IF @StuckTaskName <> 'xp_cmdshell'
BEGIN
SET @ErrorText = 'A stuck session was caused by something other than xp_cmdshell - KillStuckSessions cannot kill this process. Please remote into the server and fix.'
GOTO ErrorHandling
END
--If we get this far, we know that there's one stuck session and it is an xp_cmdshell task.
--Let's kill the task!
--Declare a bunch of variables including a table variable to store the output of our wmic commands
DECLARE @wmicdata table (ProcessID varchar(1000) NULL)
DECLARE @SQLServerPID integer
DECLARE @PIDTestA1 integer
DECLARE @PIDTestA2 integer
DECLARE @PIDTestB1 integer
DECLARE @PIDTestB2 integer
DECLARE @OrphanedPID integer
DECLARE @StuckProcess1 integer
DECLARE @StuckProcess2 integer
--First we run this to get the PID of sqlserver.exe, which is what will have spawned xp_cmdshell
INSERT @wmicdata
EXEC xp_cmdshell 'wmic process where (name="sqlservr.exe") get ProcessID'
SET @SQLServerPID = (
SELECT CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID), 8000) + 'X') -1) AS integer)
FROM @wmicdata
WHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') IS NOT NULL
AND ProcessID IS NOT NULL)
--Next, we run this twice to get the process ID of any stuck child processes of sql server.
DECLARE @cmdshellInput varchar(1000)
SET @cmdshellInput = 'wmic process where (ParentProcessId=' + CAST(@SQLServerPID AS varchar(10)) + ') get ProcessID'
--Run 1
DELETE @wmicdata
INSERT @wmicdata
EXEC xp_cmdshell @cmdshellInput
SET @PIDTestA1 = (
SELECT TOP 1 CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID), 8000) + 'X') -1) AS integer)
FROM @wmicdata
WHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') IS NOT NULL
AND ProcessID IS NOT NULL
ORDER BY ProcessID ASC)
SET @PIDTestA2 = (
SELECT TOP 1 CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID), 8000) + 'X') -1) AS integer)
FROM @wmicdata
WHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') IS NOT NULL
AND ProcessID IS NOT NULL
ORDER BY ProcessID DESC)
--Wait a second
WAITFOR DELAY '00:00:01'
--Run 2
DELETE @wmicdata
INSERT @wmicdata
EXEC xp_cmdshell @cmdshellInput
SET @PIDTestB1 = (
SELECT TOP 1 CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID), 8000) + 'X') -1) AS integer)
FROM @wmicdata
WHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') IS NOT NULL
AND ProcessID IS NOT NULL
ORDER BY ProcessID ASC)
SET @PIDTestB2 = (
SELECT TOP 1 CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID), 8000) + 'X') -1) AS integer)
FROM @wmicdata
WHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') IS NOT NULL
AND ProcessID IS NOT NULL
ORDER BY ProcessID DESC)
--The number that doesn't immediately change is our orphaned cmd.exe process.
--The number that does change is the wmic command that we're running to get the PID!
IF @PIDTestA1 = @PIDTestB1
SET @OrphanedPID = @PIDTestA1
IF @PIDTestA1 = @PIDTestB2
SET @OrphanedPID = @PIDTestA1
IF @PIDTestA2 = @PIDTestB1
SET @OrphanedPID = @PIDTestA2
IF @PIDTestA2 = @PIDTestB2
SET @OrphanedPID = @PIDTestA2
--If none of them matched, then we had a problem.
IF @OrphanedPID IS NULL
BEGIN
SET @ErrorText = 'A stuck session was caused by something other than xp_cmdshell - KillStuckSessions cannot kill this process. Please remote into the server and fix.'
GOTO ErrorHandling
END
--Next, run it again on the orhpaned cmd.exe process to find out the child processes that we want to kill
--There should be 2 tasks total which we want to kill, a conhost.exe and the actual executable which we invoked from xp_cmdshell (psftp.exe)
SET @cmdshellInput = 'wmic process where (ParentProcessId=' + CAST(@OrphanedPID AS varchar(10)) + ') get ProcessID'
DELETE @wmicdata
INSERT @wmicdata
EXEC xp_cmdshell @cmdshellInput
--Identify Stuck Process 1
SET @StuckProcess1 = (
SELECT TOP 1 CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID), 8000) + 'X') -1) AS integer)
FROM @wmicdata
WHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') IS NOT NULL
AND ProcessID IS NOT NULL
ORDER BY ProcessID ASC)
--Identify Stuck Process 2
SET @StuckProcess2 = (
SELECT TOP 1 CAST(SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID),PATINDEX('%[^0-9]%', SUBSTRING(ProcessID, PATINDEX('%[0-9]%', ProcessID), 8000) + 'X') -1) AS integer)
FROM @wmicdata
WHERE STUFF(ProcessID, 1, PATINDEX('%[0-9]%', ProcessID)-1, '') IS NOT NULL
AND ProcessID IS NOT NULL
ORDER BY ProcessID DESC)
--Get the .exe names of the processes we're about to kill
DECLARE @exe1 AS varchar(1000)
DECLARE @exe2 AS varchar(1000)
SET @cmdshellInput = 'wmic process where (ParentProcessId=' + CAST(@OrphanedPID AS varchar(10)) + ') get Name'
DELETE @wmicdata
INSERT @wmicdata
EXEC xp_cmdshell @cmdshellInput
SET @exe1 = (SELECT TOP 1 ProcessID FROM @wmicdata WHERE ProcessID LIKE '%.%' ORDER BY ProcessID ASC)
SET @exe2 = (SELECT TOP 1 ProcessID FROM @wmicdata WHERE ProcessID LIKE '%.%' ORDER BY ProcessID DESC)
--Finally, run these to kill the orphaned child tasks
SET @cmdshellInput = 'taskkill /f /pid ' + CAST(@StuckProcess1 AS varchar(10))
EXEC xp_cmdshell @cmdshellInput
SET @cmdshellInput = 'taskkill /f /pid ' + CAST(@StuckProcess2 AS varchar(10))
EXEC xp_cmdshell @cmdshellInput
--Email to say a process was killed
DECLARE @EmailBody varchar(4000)
SET @EmailBody = 'A task causing an SQL session to be stuck in KILLED/ROLLBACK was automatically killed on the server.<P>The name(s) of the executables that were killed:<P>' + @exe1 + '<P>' + @exe2
EXEC Automation.dbo.sp_SendEmailExternally --This is another stored proc of mine that sends emails. You could swap this for sp_send_dbmail
@Recipients = 'your@email.here',
@Subject = 'Agent Job KillStuckSessions found and killed a stuck session!',
@HTMLBody = @EmailBody
--Finished! Now the killed/rollback session will be gone.
--Skip the ErrorHandling section
GOTO Endpoint
ErrorHandling:
--Since We're running from an Agent Job, using RAISERROR here will generate an Alert email, and the Agent Job history will contain the custom @ErrorText message.
RAISERROR (@ErrorText, 16, 1)
EndPoint:
END
GO
这篇关于如何防止从 xp_cmdshell 运行的进程永远处于 KILLED/ROLLBACK 状态?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
