Historia jobów (job history)

Michał Pawlikowski

Źródło:

https://www.sqlmatters.com/Articles/Checking%20the%20status%20of%20SQL%20Server%20Agent%20jobs.aspx

 

USE msdb;
SELECT SJ.name AS [Job Name],
       CONVERT(
                  VARCHAR,
                  DATEADD(
                             S,
                             (SJH.run_time / 10000) * 60 * 60 /* hours */
                             + ((SJH.run_time - (SJH.run_time / 10000) * 10000) / 100) * 60 /* mins */
                             + (SJH.run_time - (SJH.run_time / 100) * 100), /* secs */
                             CONVERT(DATETIME, RTRIM(SJH.run_date), 113)
                         ),
                  20
              ) AS [Time Run],
       CASE
           WHEN SJ.enabled = 1 THEN
               'Enabled'
           ELSE
               'Disabled'
       END [Job Status],
       CASE
           WHEN SJH.run_status = 0 THEN
               'Failed'
           WHEN SJH.run_status = 1 THEN
               'Succeeded'
           WHEN SJH.run_status = 2 THEN
               'Retry'
           WHEN SJH.run_status = 3 THEN
               'Cancelled'
           ELSE
               'Unknown'
       END [Job Outcome]
FROM dbo.sysjobhistory SJH
    JOIN dbo.sysjobs SJ
        ON SJH.job_id = SJ.job_id
WHERE SJH.step_id = 0
      AND DATEADD(   S,
                     (SJH.run_time / 10000) * 60 * 60 /* hours */
                     + ((SJH.run_time - (SJH.run_time / 10000) * 10000) / 100) * 60 /* mins */
                     + (SJH.run_time - (SJH.run_time / 100) * 100), /* secs */
                     CONVERT(DATETIME, RTRIM(SJH.run_date), 113)
                 ) >= DATEADD(d, -1, GETDATE())
ORDER BY SJ.name,
         SJH.run_date,
         SJH.run_time;

 

 

Lekko zmodyfikowana:

 

USE msdb;
SELECT TOP 5
       SJ.name AS [Job Name],
       CONVERT(VARCHAR,
               DATEADD(   S,
                          (SJH.run_time / 10000) * 60 * 60 /* hours */
                          + ((SJH.run_time - (SJH.run_time / 10000) * 10000) / 100) * 60 /* mins */
                          + (SJH.run_time - (SJH.run_time / 100) * 100), /* secs */
                          CONVERT(DATETIME, RTRIM(SJH.run_date), 113)
                      ),
               20
              ) AS [Time Run],
       CASE
           WHEN SJ.enabled = 1 THEN
               'Enabled'
           ELSE
               'Disabled'
       END [Job Status],
       CASE
           WHEN SJH.run_status = 0 THEN
               'Failed'
           WHEN SJH.run_status = 1 THEN
               'Succeeded'
           WHEN SJH.run_status = 2 THEN
               'Retry'
           WHEN SJH.run_status = 3 THEN
               'Cancelled'
           ELSE
               'Unknown'
       END [Job Outcome],
       Duration = STUFF(STUFF(RIGHT('000000' + CAST(SJH.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
FROM dbo.sysjobhistory SJH
    JOIN dbo.sysjobs SJ
        ON SJH.job_id = SJ.job_id
WHERE SJH.step_id = 0
      AND DATEADD(   S,
                     (SJH.run_time / 10000) * 60 * 60 /* hours */
                     + ((SJH.run_time - (SJH.run_time / 10000) * 10000) / 100) * 60 /* mins */
                     + (SJH.run_time - (SJH.run_time / 100) * 100), /* secs */
                     CONVERT(DATETIME, RTRIM(SJH.run_date), 113)
                 ) >= DATEADD(d, -1, GETDATE())
      AND SJ.name = 'job_name'
ORDER BY SJ.name,
         [Time Run] DESC;

 

Dodaj komentarz