Pages

Wednesday, July 19, 2017

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





No comments:

Post a Comment