Pages

Wednesday, July 19, 2017

Aplicação Web para ver os SQL Server Agent Jobs de vários servidores

Problema: 
Tenho vários servidores em produção onde correr jobs críticos e preciso de ver todos os dias se correram bem, se tenho sobreposição na execução dos jobs e quando é que os jobs vão ser executados novamente.

Solução: Com base nas queries que mostrei nos posts anteriores, criei uma aplicação web em MVC no Github que resolve o problema.

A aplicação está neste URL

Para a usar é preciso configurar os servidores na secção connectionStrings do web.config e depois quando correm a aplicação é só seleccionar os servidores na secção Servers para ver o agendamento agregado e as datas da próxima execução.





O código é disponibilizado as is, actualmente uso numa aplicação para saber o que acontece em produção nos vários servidores com mais uns pózinhos específicos para o meu caso.

Se ajudar fixe, mas não dou garantias de nada :)



Query para obter a data da próxima execução dos Jobs no SQL Server Agent

Esta query basicamente retorna o que vemos no "Job Activity Monitor" na coluna "Next Run" do SQL Server Agent


query retorna as seguintes colunas:
  • JobIDGuid que identifica o Job
  • Name: Nome do Job
  • StartDate: Data/hora em que o Job vai executar


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT 
     sysjobs.job_id AS JobId
    ,sysjobs.name AS Name
    ,MAX(sysjobactivity.next_scheduled_run_date) AS StartDate
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobactivity ON (sysjobactivity.job_id = sysjobs.job_id)
WHERE sysjobactivity.next_scheduled_run_date > GETDATE()
GROUP BY 
     sysjobs.job_id
    ,sysjobs.name 



Query para obter Jobs a executar no SQL Server Agent

Esta query basicamente retorna o que vemos no "Job Activity Monitor" do SQL Server Agent com mais uns pózinhos :)


query retorna as seguintes colunas:
  • JobIDGuid que identifica o Job
  • Name: Nome do Job
  • StartDate: Data/hora em que o Job começou
  • EndDate: Data/hora em que o Job terminou
  • Duration: Duração do Job em segundos
  • ResultStatus: Resultado da execução do Job
  • ResultMessage: Mensagem com os detalhes do resultado da execução do Job


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT 
     sysjobs.job_id AS JobID
    ,sysjobs.name AS Name
    ,sysjobactivity.start_execution_date AS StartDate
    ,GETDATE() AS EndDate
    ,DATEDIFF(SECOND, sysjobactivity.start_execution_date, GETDATE()) AS Duration
    ,'Running' AS ResultStatus
    ,'The job is running...' AS ResultMessage
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobactivity ON (sysjobactivity.job_id = sysjobs.job_id)
WHERE sysjobactivity.job_history_id IS NULL
    AND sysjobactivity.start_execution_date IS NOT NULL



Query para obter histórico dos Jobs no SQL Server Agent

Esta query basicamente retorna o que vemos no "View History" do SQL Server Agent com mais uns pózinhos :)


query retorna as seguintes colunas:
  • JobIDGuid que identifica o Job
  • Name: Nome do Job
  • StartDate: Data/hora em que o Job começou
  • EndDate: Data/hora em que o Job terminou
  • Duration: Duração do Job em segundos
  • ResultStatus: Resultado da execução do Job
  • ResultMessage: Mensagem com os detalhes do resultado da execução do Job


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT 
     sysjobs.job_id AS JobID
    ,sysjobs.name AS Name
    ,CAST(
        CAST(sysjobhistory.run_date AS VARCHAR) 
        + ' ' 
        + STUFF(STUFF(RIGHT('000000' + CAST(sysjobhistory.run_time AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':')
    AS DATETIME) AS StartDate
    ,DATEADD(HOUR, CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 1, 2) AS INT)
        ,DATEADD(MINUTE, CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 3, 2) AS INT)
            ,DATEADD(SECOND, CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 5, 2) AS INT)
                ,CAST(
                    CAST(sysjobhistory.run_date AS VARCHAR) 
                    + ' ' 
                    + STUFF(STUFF(RIGHT('000000' + CAST(sysjobhistory.run_time AS VARCHAR), 6), 3, 0, ':'), 6, 0, ':')
                AS DATETIME)
            )
        )
    ) AS EndDate
    ,(
        CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 1, 2) AS INT) * 3600 -- HOURS
      + CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 3, 2) AS INT) * 60   -- MINUTES
      + CAST(SUBSTRING(RIGHT('000000' + CAST(sysjobhistory.run_duration AS VARCHAR), 6), 5, 2) AS INT)        -- SECONDS
    ) AS Duration
    ,CASE 
        WHEN sysjobhistory.run_status = 0 THEN 'Failed'
        WHEN sysjobhistory.run_status = 1 THEN 'Success'
        WHEN sysjobhistory.run_status = 4 THEN 'Running'
        ELSE 'Others (' + CAST(sysjobhistory.run_status AS VARCHAR) + ')'
    END AS ResultStatus
    ,sysjobhistory.[message] AS ResultMessage
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobhistory ON (sysjobhistory.job_id = sysjobs.job_id)
WHERE sysjobhistory.step_id = 0 -- GET JOB RESULT