-- 아래 스크립트를 사용하여 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
