Forcing a specific stored procedure to stop after 30 seconds: I do not want to reconfigure the timeout of the entire database

Question

(The reason I need the following doesn't matter)

What I need to do is set up the following so that it executes a stored procedure, which usually takes 30 minutes, but then the procedure stops after a set time of 60 seconds - almost the same as if I were in the SSMSprocedure and press the cancel button after 60 seconds .

I don’t want to reconfigure all db so that every other long-running stored procedure fails after 30 seconds - only a specific procedure TESTexecuteLongRunningProc.

The verification procedure is called here:

CREATE PROCEDURE [dbo].[TESTlongRunningProc] 
AS
BEGIN

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

END;

This is a proc that I would like to configure, so it cancels itself after 60 seconds:

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

. , . " ". -

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

,

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

, . , , . , , SQL . , , , (.. , , , ).

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

Answer 2

, SQL Server, , sp_start_job sp_stop_job .

- , :

-- control procedure


declare @starttime DATETIME = SYSDATETIME()

exec msdb..sp_start_job 'Job' -- The job containing the target procedure that takes 30 minutes

while 1>0

    BEGIN
        -- Check to see if the job is still running and if it has been running long enough
        IF EXISTS(
                        SELECT TOP 1 b.NAME
                        FROM msdb..sysjobactivity a
                        INNER JOIN msdb..sysjobs b
                               ON a.job_id = b.job_id
                        WHERE start_execution_date >= @starttime
                               AND stop_execution_date IS NULL
                               AND b.NAME in ('job')
                               and DATEDIFF(second,start_execution_date,SYSDATETIME()) >= 60
                  ) 
        BEGIN

            exec msdb..sp_stop_job 'Job'

        END

    waitfor delay '00:00:05';

END