Monitorando Jobs em execução no SQL Server

Neste artigo irei mostrar como criar um procedimento para monitorar Jobs em execução no SQL Server.

Nativamente o SQL Server oferece meios de enviar alertas caso um processo agendado falhe ou tenha sua execução finalizada. Porém, em alguns casos pode ocorrer do Job ficar em execução por um tempo infinito, “travado”, ou você pode querer ser alertado caso ele ultrapasse uma determinada janela de execução.

Para este propósito, desenvolvemos uma simples procedure para enviar um alerta quando um job ultrapassar seu tempo máximo de execução. Este tempo máximo será definido para cada Job e cadastrado em uma tabela de configuração. Este procedimento foi desenvolvido e testado no SQL Server 2005.

Primeiramente devemos configurar o Database Mail e cadastrar os operadores que receberão os alertas. Caso tenham alguma dúvida para realizar esta configuração, me escrevam e terei o maior prazer em auxiliá-los.

Depois de realizada esta configuração e testado o envio de e-mails pelo SQL Server, vamos criar as tabelas que irão nos auxiliar neste processo

Tabela de configuração que irá armazenar os Jobs que serão monitorados, contendo o nome do Job, o tempo de execução limite (em minutos) e operador que irá receber o alerta. Este operador já deve estar cadastrado. Para realizar o cadastro vá ao Management Studio em SQL Server Agent->operators. Será para o e-mail cadastrado no operators que os alertas serão enviados.

CREATE TABLE [dbo].[job_conf](
    [job_name] [nvarchar](50) NULL,
    [time_limit] [numeric](5, 0) NULL,
    [operator] [nvarchar](30) NULL
) ON [PRIMARY]

Tabela que irá armazenar temporariamente as informações sobre os Jobs em execução:

CREATE TABLE [dbo].[Job_Monitor](
    [session_id] [int] NOT NULL,
    [job_id] [uniqueidentifier] NOT NULL,
    [job_name] [nvarchar](128) NOT NULL,
    [run_requested_date] [datetime] NULL,
    [run_requested_source] [nvarchar](128) NULL,
    [queued_date] [datetime] NULL,
    [start_execution_date] [datetime] NULL,
    [last_executed_step_id] [int] NULL,
    [last_executed_step_date] [datetime] NULL,
    [stop_execution_date] [datetime] NULL,
    [next_scheduled_run_date] [datetime] NULL,
    [job_history_id] [int] NULL,
    [message] [nvarchar](1024) NULL,
    [run_status] [int] NULL,
    [operator_id_emailed] [int] NULL,
    [operator_id_netsent] [int] NULL,
    [operator_id_paged] [int] NULL
) ON [PRIMARY]

E finalmente o procedimento armazenado que irá percorrer cada job cadastrado na tabela job_conf, verificar se está em execução e ultrapassou o tempo limite e enviar um alerta ao operador cadastrado.

/****** Object: StoredProcedure [dbo].[job_monitor_exec]
                  Opensys Soluções Integradas ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[job_monitor_exec]
as
Begin
Declare @limit numeric,
@tempo_execucao numeric,
@job_name varchar(50),
@start_execution_date datetime,
@stop_execution_date datetime,
@oper_email nvarchar(500),
@oper nvarchar(30),
@body_msg nvarchar(500),
@subject_msg nvarchar(100) 
DECLARE conf_jobs CURSOR FOR
select * from  job_conf

Delete from job_monitor
Insert into job_monitor exec msdb.dbo.sp_help_jobactivity

OPEN conf_jobs
FETCH NEXT FROM conf_jobs INTO @job_name,@limit, @oper
WHILE @@FETCH_STATUS = 0
BEGIN
  select @oper_email = email_address from msdb.dbo.sysoperators where name = @oper
  select @start_execution_date = start_execution_date, @stop_execution_date = stop_execution_date
  from job_monitor where job_name = @job_name
  set @tempo_execucao = datediff(minute,@start_execution_date,getdate())
  set @body_msg = 'Tempo Limite do Job ' + @job_name + ' Excedido <BR>' + 'Tempo em Execucao (Min): ' + cast(@tempo_execucao as nvarchar)+ '<BR> Tempo Limite (Min): ' + cast(@limit as nvarchar) + '<BR>'
  set @subject_msg = 'Tempo Limite do Job ' + @job_name + ' Excedido'
  if @start_execution_date is not null
     and @stop_execution_date is null 
     and   @tempo_execucao > @limit
     Begin
         EXEC msdb.dbo.sp_send_dbmail
         @recipients=@oper_email,
         @body=@body_msg,@body_format = 'HTML' ,
         @importance ='High',
         @subject =@subject_msg,
         @profile_name ='Nome do Profile no Database Mail'; -- Nome do Profile cadastrado no Database Mail
     End
  FETCH NEXT FROM conf_jobs INTO @job_name,@limit, @oper
END
CLOSE conf_jobs
DEALLOCATE conf_jobs

END

O procedimento job_monitor_exec somente verifica se o Job ultrapassou seu tempo limite e envia um alerta via E-mail. 

Você pode customizá-lo para que outras ações sejam executadas, além do alerta, como, por exemplo, parar o Job e executá-lo novamente, executar outro Job ou procedimento armazenado, etc.

Agende a execução deste procedimento para cada 10 ou 5 minutos.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'AUDITOR Monitor Jobs',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Monitora Jobs travados',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa',
        @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [job_monitor_exec]    Script Date: 05/23/2012 14:15:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'job_monitor_exec',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'exec job_monitor_exec',
        @database_name=N'Auditor', /* Nome da Base de Dados onde o procedimento armazenado foi criado*/
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'job_monitor_exec recurring',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=4,
        @freq_subday_interval=10,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20120523,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: