etl作业超时处理

USE [master]
GO
/****** Object: StoredProcedure [dbo].[PR_YWK] Script Date: 09/29/2000 05:04:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PR_YWK]
AS
Declare @countNeedKill int;
BEGIN
SELECT @countNeedKill = COUNT(1) from (
select
job_name,
run_datetime,
run_duration AS run_duration
from
(
select
job_name,
DATEADD(hh, -7, run_datetime) as run_datetime,
h.run_duration
from
(
select
j.name as job_name,
run_datetime = max(CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
where j.name = 作业名称'
group by j.name
) t
inner join msdb..sysjobs j
on t.job_name = j.name
inner join msdb..sysjobhistory h
on j.job_id = h.job_id and
t.run_datetime = CONVERT(DATETIME, RTRIM(h.run_date)) + (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4
) dt where run_duration > 60
)t;
if (@countNeedKill > 0)
begin
EXEC msdb.dbo.sp_update_job
@job_name = N'作业名称',
@new_name = N'作业名称',
@description = N'',
@enabled = 0 ;--1 启用作业 ，0 禁用作业
EXEC msdb.dbo.sp_update_job
@job_name = N'作业名称',
@new_name = N'作业名称',
@description = N'',
@enabled = 1 ;--1 启用作业 ，0 禁用作业
end
END