MSSQL Long Running Job Check

-- 아래 스크립트를 사용하여 Long Running Check Job을 '[DBA] Long Running Job Check' 이름으로 생성

declare @JOB_INTERVAL_SEC table
(
	job_id   uniqueidentifier
,	run_interval_sec int
)

insert into @JOB_INTERVAL_SEC
select	job_id, datediff(SECOND, min(run_datetime_2), min(run_datetime_1)) as run_interval_sec
from 
(
	select	job_id
		,	case when job_num = 1 then run_datetime else getdate() end AS run_datetime_1
		,	case when job_num = 2 then run_datetime else getdate() end AS run_datetime_2
	from 
	(
		select	job_id
			,	convert(datetime, format(run_date,'####-##-##') + ' ' + format(run_time,'####:##:##'), 120) AS run_datetime
			,	row_number() over(partition by job_id order by  instance_id desc ) AS job_num
		from [msdb].[dbo].[sysjobhistory]
		where step_id = 0
	) AS T
	where job_num in (1,2)
) TT
group by job_id

--select * from @JOB_INTERVAL_SEC

declare	@cur_job_name sysname
	,	@cur_job_id uniqueidentifier
	,	@cur_start_execution_date datetime
	,	@cur_stop_execution_date datetime
	,	@cur_last_executed_step_date datetime
	,	@cur_next_scheduled_run_date datetime
	,	@cur_run_interval_sec int
	,	@cur_running_second int
	,	@cur_error_message varchar(1024)

declare cur cursor for

select	jam.job_name, jam.job_id
	,	sja.start_execution_date, sja.stop_execution_date, sja.last_executed_step_date, sja.next_scheduled_run_date
	,	JIS.run_interval_sec
	,	datediff(second, start_execution_date, getdate()) AS running_second
from
(
	select max(sa.session_id) AS session_id_max, sa.job_id, sj.name AS job_name from msdb.dbo.sysjobactivity sa
	inner join msdb.dbo.sysjobs sj
	on sa.job_id = sj.job_id
	where sj.enabled = 1
	group by  sa.job_id, sj.name
)  jam
inner join  msdb.dbo.sysjobactivity sja
on jam.session_id_max = sja.session_id
and jam.job_id = sja.job_id
inner join @JOB_INTERVAL_SEC JIS
on sja.job_id = JIS.job_id
where sja.stop_execution_date IS NULL
and datediff(second, start_execution_date, getdate()) > JIS.run_interval_sec

open cur
fetch next from cur into @cur_job_name, @cur_job_id, @cur_start_execution_date, @cur_stop_execution_date, @cur_last_executed_step_date, @cur_next_scheduled_run_date, @cur_run_interval_sec, @cur_running_second
		
while @@fetch_status = 0
begin
	set @cur_error_message = '[error] : the job schedule has not yet ended !!   ';
	set @cur_error_message = @cur_error_message + '[job_name] : ' + convert(varchar(100),@cur_job_name) + ' [cur_job_id] : ' + convert(varchar(100),@cur_job_id) + ' [cur_start_execution_date] : ' + convert(varchar(100),@cur_start_execution_date,121)
							+ ' [cur_run_interval_sec] : ' + convert(varchar(100),@cur_run_interval_sec) + ' [cur_running_second] : ' + convert(varchar(100),@cur_running_second)
							--+ ' cur_stop_execution_date : ' + convert(varchar(100),@cur_stop_execution_date,121) + ' cur_last_executed_step_date : ' + convert(varchar(100),@cur_last_executed_step_date) + ' cur_next_scheduled_run_date : ' + convert(varchar(100),@cur_next_scheduled_run_date)							
	
	--select @cur_job_name, @cur_run_interval_sec, @cur_running_second
	--select @cur_error_message
	if @cur_job_name <>  '[DBA] Long Running Job Check'
	BEGIN
		RAISERROR(@cur_error_message, 16,1) 
	END

	fetch next from cur into @cur_job_name, @cur_job_id, @cur_start_execution_date, @cur_stop_execution_date, @cur_last_executed_step_date, @cur_next_scheduled_run_date, @cur_run_interval_sec, @cur_running_second
end

close cur
deallocate cur