Ограничить время работы заданий - джобов в MS SQL Server

У любого администратора БД есть n-е количество заданий на серверах, как минимум то 1 для обслуживания БД точно должно быть, которое вы запускаете по расписанию, когда будет минимальное влияние на обслуживание клиентов БД. Вы рассчитываете, что когда начнется основная активность, ваше задание уже не будет работать. Или, к примеру, есть какое-то задание, которое должно выполняться не более n-е количество времени  и не больше.

Как отследить за работой заданий ?! Как ограничить работу его по времени?! Как узнать, чт задание выполняется сверх нормы?! Ниже я описал как это сдлеать двумя распространёнными методами.

Итак, метод 1 - самый простой и наиболее точный ограничивающий по времени работу задания. Суть его заключается , что когда ваше задание начинается, запускается второй джоб, который после установленного времени, должен остановить ваше основное задание.

Для этого перед выполнением кода основного джоба вставляем код:

EXEC msdb .dbo.sp_start_job N'монитор работы джоба'

Go

…ваш основной код

Процедура dbo.sp_start_job запускает наш второй джоб N'монитор работы джоба'.

В коде второго задания пишем код:

WAITFOR DELAY '00:01:00'--указываем время, через которое необходимо прерывание задание

EXEC msdb.dbo.sp_stop_job N'test'-- задание, которое необходимо прервать

Все, после теперь наш джоб не будет выполнять дольше, чем надо.

Метода 2- это что-то уже вроде системы мониторинга за заданиями, так же основывается на специальном джобе, который запускается по расписанию, смотрит активность заданий, их продолжительность.

1-е создадим таблицу, где будут содержаться наши данные по каким джобам наблюдать, критическое время их выполнения и тип реагирования.

USE [msdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[jobs_monitoring](

    [job_id] [uniqueidentifier] NOT NULL,  --job_id джоба, берем из sysjobs

    [job_name] [nvarchar](128) NULL,   -- имя джоба, можно произвольное,     можно из sysjobs, влияет только на отображение в алерте

    [max_time] [int] NULL,  -- предельное время работы задания

    [operators_notification] [nvarchar](256) NULL,  --- e-mail-ы оповещения,

    [sms_mail] [nvarchar](256) NULL,  -- e-mail оповещения для смс

    [interrupt] [bit] NULL, -- прерывать задание или нет ( 1- прерывать, 0 - только оповещение)

 CONSTRAINT [PK_jobs_monitoring] PRIMARY KEY CLUSTERED

(

    [job_id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Заполняете таблицу исходными данными, job_id  берем из msdb.dbo.sysjobs,  имя джоба можно указать, то которое вам ближе, ну и оставшиеся данные: критическое время, через которое необходимо реагировать, прерывать или только оповещение, адреса отправки оповещений на почту, и, если используется, адрес через который идет отправка смс на телефон.

2- е процедура

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=localhost;Trusted_Connection=yes;', 'exec msdb.dbo.sp_help_jobactivity ')

    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=localhost;Trusted_Connection=yes;', 'exec msdb.dbo.sp_help_job @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 -- конец процедуры

3- е, теперь необходимо  нашу процедуру adminSP_Jobs_monitoring добавить в задание, назначить расписание, и все мониторинг заданий работает.

Есть как плюсы, так и минусы обоих подходов:

Метод1 подходит, когда необходимо более точно определить время работы задания, минус, данный код нужно прописывать в ко каждого задания.

Метод 2 это более универсальный джоб, который мониторит сразу несколько заданий, т.е один на всех, если нужно добавить джоб в мониторинг , то добавляем данные в таблицу jobs_monitoring, и все задание на мониторинге, легко задать нужное расписание мониторинга. Минус расписание джоба может попасть, когда джоб выполняется долго, а при следующем запуске, он уже завершился. Не стоит забывать и об откате транзакции при остановке джоба, если джоб производил изменения 30 минут, то при остановке примерно столько же и будет откат транзакций.

Оба эти два метода являются основой для ваших дальнейших систем мониторинга, дальше вы можете модифицировать код для себя, подстроить расписании для метода 2 и так далее.

И еще, самое главное, основное назначение данных решений, это все таки мониторинг работы заданий, т.е если ваше обычное задание выполняется за 1-2 минуту, а тут вдруг стало выполняться 5 -10 ,а то и 20 минуту, то тут явно в чем-то проблема: загруженность сервера, СУБД, проблемы с железом, неоптимальный код при других данных и т.д. , то есть над чем подумать.

P.S: чуть позже поправлю оформление данного поста., неудобно на ноуте все таки работать:)