SQL代理：设置最长执行时间
下午。我在MS 2K8 BI服务器上运行了多个SQL代理作业，其中一些是每天运行，另一些是每小时运行一次，每两分钟运行一次（另一个进程的心跳监视器）。还有一个应用程序，每隔几分钟就可以全天候导入数据。偶尔会有一些更新和报告的组合发生冲突，一个或另一个会挂起半小时或更长时间，而不是通常的60秒。
虽然我需要了解这些竞争条件的根源，但与此同时，我希望将某些工作设置为在五分钟之后自动死亡。我可以在SSIS或Windows计划任务中执行此操作，但我在SQL Agent中看不到任何方法。这是可能的，还是我需要将任务包装在SSIS包中以获得这种控制？
仅供参考，这是我最终使用的SQL Agent作业：
DECLARE @Cancelled BIT
EXEC dbo.CancelJob @JobName = 'ETL - Daily', @Cancelled = @Cancelled OUT
IF @Cancelled = 1
BEGIN
DECLARE @Success INT
EXEC @Success = msdb..sp_send_dbmail
@profile_name = 'Reporting',
@recipients = 'reporting@mycompany.com',
@subject = 'Cancelled Daily ETL'
IF @Success <> 0 RAISERROR('An error occurred while attempting to send an e-mail.', 16, @Success)
END
CancelJob
CREATE PROCEDURE dbo.CancelJob(@JobName VARCHAR(100), @OwnerName VARCHAR(100) = NULL, @Cancelled BIT OUT)
AS BEGIN
IF @OwnerName IS NULL SET @OwnerName = SUSER_NAME()
SET @Cancelled = 0
CREATE TABLE #JobInfo
(
Job_ID UNIQUEIDENTIFIER,
Last_Run_Date INT,
Last_Run_Time INT,
Next_Run_Date INT,
Next_Run_Time INT,
Next_Run_Schedule_ID INT,
Requested_To_Run INT,
Request_Source INT,
Request_Source_ID VARCHAR(100),
Running INT, -- This is the only field we want (sigh)
Current_Step INT,
Current_Retry_Attempt INT,
State INT
)
INSERT INTO #JobInfo
EXEC xp_sqlagent_enum_jobs 1, @OwnerName
DECLARE @Running INT = (SELECT Running FROM #JobInfo AS JI INNER JOIN msdb..sysjobs_view AS J ON JI.Job_ID = J.job_id WHERE J.name = @JobName)
IF @Running = 1
BEGIN
BEGIN TRY
EXEC msdb..sp_stop_job @job_name = @JobName
SET @Cancelled = 1
END TRY
BEGIN CATCH
-- If an error occurs, it is *probably* because the job finished before we could cancel it, which is fine
END CATCH
END
END
GO
xp_sqlagent_enum_jobs
没有找到相关结果
已邀请:
1 个回复
疼嘶桐