SQL Server 代理作业超时 [英] SQL Server Agent Job Timeout
问题描述
我刚刚让一个预定的 SQL Server 作业运行的时间比平时长,我真的可以设置超时以在一定时间后停止它.
I have just had a scheduled SQL Server job run for longer than normal, and I could really have done with having set a timeout to stop it after a certain length of time.
我可能对此有点盲目,但我似乎找不到设置作业超时的方法.有人知道怎么做吗?
I might be being a bit blind on this, but I can't seem to find a way of setting a timeout for a job. Does anyone know the way to do it?
谢谢
推荐答案
作为夜间作业处理子系统的一部分,我们做了类似下面的代码——实际上比这更复杂;例如,我们正在处理多个相互依赖的作业集,并从配置表中读取作业名称和超时值 - 但这抓住了这个想法:
We do something like the code below as part of a nightly job processing subsystem - it is more complicated than this actually in reality; for example we are processing multiple interdependent sets of jobs, and read in job names and timeout values from configuration tables - but this captures the idea:
DECLARE @JobToRun NVARCHAR(128) = 'My Agent Job'
DECLARE @dtStart DATETIME = GETDATE(), @dtCurr DATETIME
DECLARE @ExecutionStatus INT, @LastRunOutcome INT, @MaxTimeExceeded BIT = 0
DECLARE @TimeoutMinutes INT = 180
EXEC msdb.dbo.sp_start_job @JobToRun
SET @dtCurr = GETDATE()
WHILE 1=1
BEGIN
WAITFOR DELAY '00:00:10'
SELECT @ExecutionStatus=current_execution_status, @LastRunOutcome=last_run_outcome
FROM OPENQUERY(LocalServer, 'set fmtonly off; exec msdb.dbo.sp_help_job') where [name] = @JobToRun
IF @ExecutionStatus <> 4
BEGIN -- job is running or finishing (not idle)
SET @dtCurr=GETDATE()
IF DATEDIFF(mi, @dtStart, @dtCurr) > @TimeoutMinutes
BEGIN
EXEC msdb.dbo.sp_stop_job @job_name=@JobToRun
-- could log info, raise error, send email etc here
END
ELSE
BEGIN
CONTINUE
END
END
IF @LastRunOutcome = 1 -- the job just finished with success flag
BEGIN
-- job succeeded, do whatever is needed here
print 'job succeeded'
END
END
这篇关于SQL Server 代理作业超时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!