sql-server - 强制特定存储过程在 30 秒后停止 : I do not want to reconfigure the timeout of the whole DB

Question

(我需要以下内容的原因并不重要)

我想要做的是调整以下内容，以便它执行存储过程，这通常需要 30 分钟，但该过程会在 60 秒的设定时间后停止 - 实际上与我在 SSMS 运行该程序并在 60 秒后按取消按钮。

我不想重新配置整个数据库，以便其他所有长时间运行的存储过程在 30 秒后超时 - 只有特定过程 TESTexecuteLongRunningProc。

这里是被调用的测试过程:

CREATE PROCEDURE [dbo].[TESTlongRunningProc] 
AS
BEGIN

   --placeholder that represents the long-running proc
   WAITFOR DELAY '00:30:00';

END;

这是我想要调整的过程，以便它在 60 秒后自行取消:

CREATE PROCEDURE [dbo].[TESTexecuteLongRunningProc] 
AS
BEGIN

    EXECUTE WH.dbo.TESTlongRunningProc;

    -->>here I would like some code that cancels TESTexecuteLongRunningProc after 60 seconds

END;

Answer 1

本质上，您可以创建一个单独的进程来监视后台的特定任务和指标，并在必要时终止。让我们首先将跟踪设备植入您想要跟踪的代码中。我使用了带有关键词“杀了我”的评论 block 。您可以在原始代码中放置类似的内容

CREATE PROCEDURE TrackedToKill
-- EXEC TrackedToKill
/* Comment Block tracking device: Kill Me*/
AS
BEGIN
DECLARE @Counter bigint = 0
WHILE 1 = 1
BEGIN
SET @Counter = @Counter + 1
WAITFOR DELAY '00:00:30'
END
END

然后让我们看看是否可以找到正在运行的 session

SELECT session_id,
command,database_id,user_id,
wait_type,wait_resource,wait_time,
percent_complete,estimated_completion_time,
total_elapsed_time,reads,writes,text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text (sys.dm_exec_requests.sql_handle)
WHERE text LIKE '%Kill Me%'
AND session_id <> @@SPID

好的，这应该会返回与您的跟踪设备的 session 。然后，我们可以将其转换为另一个存储过程，该存储过程将根据跟踪设备和您可能需要的任何其他标准终止您的进程。您可以手动启动它，也可以在启动时使用 SQL 代理启动它。包括您需要的尽可能多的附加条件，以确保限制您要终止的范围(即尚未回滚的用户、数据库、 block 或进程)。

CREATE PROCEDURE HunterKiller
-- EXEC  HunterKiller
AS
BEGIN
DECLARE @SessionToKill int
DECLARE @SQL nvarchar(3000)
WHILE 1=1
BEGIN
SET @SessionToKill = (SELECT TOP 1 session_id
    FROM sys.dm_exec_requests
    CROSS APPLY sys.dm_exec_sql_text (sys.dm_exec_requests.sql_handle)
    WHERE session_id <> @@SPID 
    AND text LIKE '%Kill Me%'
    AND total_elapsed_time >= 15000)
SET @SQL = 'KILL ' + CONVERT(nvarchar,@SessionToKill)
EXEC (@SQL)
WAITFOR DELAY '00:00:05'
END
END