USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[adminSP_Jobs_monitoring] @mail_profile nvarchar(50)
as
begin
    SET NOCOUNT ON;

declare @dt datetime  -- время на которое будет сбор данных, сделал как константу, т.к вдруг через секунду закончится работать джоб,
-- а рассыка произойдет, чтобы в письме отсылки отразить, на какой момент работал джоб долгое время.
set @dt=GETDATE()

select t2.job_id,t2.name,t2.originating_server,t1.start_execution_date,
        t1.dtdiff, t3.operators_notification,t3.sms_mail,t3.interrupt
into #res
 from
    (
    select job_id,job_name,start_execution_date,DATEDIFF(mi,start_execution_date,GETDATE())  as dtdiff
    from
    openrowset('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', '
	
	DECLARE
	@session_id              int = NULL,              
    @job_id                  uniqueidentifier = NULL, 
    @job_name                sysname = NULL          
    
	
	EXEC msdb.dbo.sp_help_jobactivity 
	@session_id,
	@job_id,
	@job_name               
    
	WITH RESULT SETS
	(
	  (
	session_id              int,          
    job_id                  uniqueidentifier, 
    job_name                sysname,
    run_requested_date      datetime, 
    run_requested_source    sysname, 
    queued_date             datetime,
    start_execution_date    datetime, 
    last_executed_step_id   int, 
    last_exectued_step_date datetime, 
    stop_execution_date     datetime, 
	next_scheduled_run_date datetime, 
    job_history_id          int, 
    message                 nvarchar(1024), 
    run_status              int, 
    operator_id_emailed     int, 
    operator_id_netsent     int,
    operator_id_paged       int
	)
	)
	')
    where  job_id in (select job_id  from msdb.dbo.jobs_monitoring)) as t1
inner join (
    select job_id,name,originating_server --into #works_jobs
    from
    openrowset('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'exec msdb.dbo.sp_help_job_with_results @execution_status =0')
    where  job_id in (select job_id  from msdb.dbo.jobs_monitoring)) as t2 on t1.job_id=t2.job_id
inner join msdb.dbo.jobs_monitoring t3 on t1.job_id=t3.job_id
where t1.dtdiff>t3.max_time

---отсылка курсором на основе результата запроса
declare @jname varchar(128)
declare @server varchar(50)
declare @dtstart datetime
declare @dtdiff int
declare @email varchar(100)
declare @body varchar(350)
declare @smsmail varchar(100)
declare @break int
declare @job_id uniqueidentifier
select * from #res  --это проверял результаты
declare cur cursor for select job_id,name,originating_server,start_execution_date,dtdiff,operators_notification,sms_mail,interrupt  from #res
open cur

fetch next from cur into @job_id,@jname,@server,@dtstart,@dtdiff,@email,@smsmail,@break

while @@FETCH_STATUS<>-1
begin
    --текст тела письма с форматирование даты в формат дд/мм/гггг чч/мм/сс
    set @body='Задание "'+@jname+'" на сервере '+@server +' начавшееся в '+convert(varchar(2),datepart(dd,@dtstart))+'/'+convert(varchar(2),datepart(mm,@dtstart))+'/'+convert(varchar(4),datepart(yyyy,@dtstart))+
    ' '+convert(varchar(4),datepart(hh,@dtstart))+':'+convert(varchar(4),datepart(n,@dtstart))+':'+convert(varchar(4),datepart(ss,@dtstart))
    + ' выполняется уже '+convert(varchar(10),@dtdiff)+' минут(ы). Время проверки: '+
convert(varchar(2),datepart(dd,@dt))+'/'+convert(varchar(2),datepart(mm,@dt))+'/'+convert(varchar(4),datepart(yyyy,@dt))+
    ' '+convert(varchar(4),datepart(hh,@dt))+':'+convert(varchar(4),datepart(n,@dt))+':'+convert(varchar(4),datepart(ss,@dt))

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @mail_profile,
         @recipients = @email,
         @subject = 'Долго выполняется задание: ',
         @body = @body
    -- остановка джоба,
    if @break =1
    begin
     exec msdb.dbo.sp_stop_job @job_id=@job_id

    end
    -----
    if (@smsmail is not  null  or @smsmail!='')
    begin
    set @body='Задание "'+@jname+'" на сервере '+@server +'выполняется более '+convert(varchar(10),@dtdiff)+' мин.'
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @mail_profile,
         @recipients = @smsmail,
         @subject = 'Долго выполняется задание: ',
         @body = @body
    --print 'модуль отсылки смс'
    end
   
    set @body=''
    fetch next from cur into @job_id,@jname,@server,@dtstart,@dtdiff,@email,@smsmail,@break
end
drop table #res
close cur
deallocate  cur
end -- конец процедуры