SQLServer JOBに関する実行状態を問い合わせる文

作業基本情報と作業実行状況の照会

SELECT
    [jop].[job_id] AS '       '
   ,[jop].[name] AS '    '
   ,[dp].[name] AS '     '
   ,[cat].[name] AS '    '
   ,[jop].[description] AS '    '
   , CASE [jop].[enabled]
        WHEN 1 THEN ' '
        WHEN 0 THEN ' '
      END AS '    '
   ,[jop].[date_created] AS '      '
   ,[jop].[date_modified] AS '        '
   ,[sv].[name] AS '         '
   ,[step].[step_id] AS '      '
   ,[step].[step_name] AS '    '
   , CASE
        WHEN [sch].[schedule_uid] IS NULL THEN ' '
          ELSE ' '
      END AS '       '
   ,[sch].[schedule_uid] AS '          '
   ,[sch].[name] AS '           '
   , CASE [jop].[delete_level]
        WHEN 0 THEN '   '
        WHEN 1 THEN '     '
        WHEN 2 THEN '     '
        WHEN 3 THEN '     '
      END AS '        '
FROM [msdb].[dbo].[sysjobs] AS [jop]
LEFT JOIN [msdb].[sys].[servers] AS [sv]
         ON [jop].[originating_server_id] = [sv].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [cat]
         ON [jop].[category_id] = [cat].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [step]
         ON [jop].[job_id] = [step].[job_id]
            AND [jop].[start_step_id] = [step].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [dp]
         ON [jop].[owner_sid] = [dp].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [jsch]
         ON [jop].[job_id] = [jsch].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sch]
         ON [jsch].[schedule_id] = [sch].[schedule_id]
ORDER BY [jop].[name]

作業最後実行状況

SELECT
    [job].[job_id] AS '       '
   ,[job].[name] AS '    '
   , CASE WHEN [jobh].[run_date] IS NULL
               OR [jobh].[run_time] IS NULL THEN NULL
          ELSE CAST ( CAST ([jobh].[run_date] AS CHAR ( 8 )) + ' '
               + STUFF(STUFF( RIGHT ( '000000'
                                   + CAST ([jobh].[run_time] AS VARCHAR ( 6 )), 6 ),
                             3 , 0 , ':' ), 6 , 0 , ':' ) AS DATETIME)
     END AS '      '
   , CASE [jobh].[run_status]
      WHEN 0 THEN '  '
      WHEN 1 THEN '  '
      WHEN 2 THEN '  '
      WHEN 3 THEN '  '
      WHEN 4 THEN '    '
     END AS '      '
   ,STUFF(STUFF( RIGHT ( '000000' + CAST ([jobh].[run_duration] AS VARCHAR ( 6 )), 6 ),
                 3 , 0 , ':' ), 6 , 0 , ':' ) AS '        '
   ,[jobh].[message] AS '        '
   , CASE [jsch].[NextRunDate]
      WHEN 0 THEN NULL
       ELSE CAST ( CAST ([jsch].[NextRunDate] AS CHAR ( 8 )) + ' '
            + STUFF(STUFF( RIGHT ( '000000'
                                + CAST ([jsch].[NextRunTime] AS VARCHAR ( 6 )),
                                6 ), 3 , 0 , ':' ), 6 , 0 , ':' ) AS DATETIME)
     END AS '      '
FROM [msdb].[dbo].[sysjobs] AS [job]
LEFT JOIN (
             SELECT
                [job_id]
               , MIN ([next_run_date]) AS [NextRunDate]
               , MIN ([next_run_time]) AS [NextRunTime]
             FROM [msdb].[dbo].[sysjobschedules]
             GROUP BY [job_id]
          ) AS [jsch]
         ON [job].[job_id] = [jsch].[job_id]
LEFT JOIN (
             SELECT
                [job_id]
               ,[run_date]
               ,[run_time]
               ,[run_status]
               ,[run_duration]
               ,[message]
               ,ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC , [run_time] DESC ) AS RowNumber
             FROM [msdb].[dbo].[sysjobhistory]
             WHERE [step_id] = 0
          ) AS [jobh]
     ON [job].[job_id] = [jobh].[job_id]
        AND [jobh].[RowNumber] = 1
ORDER BY [job].[name]

 
作業手順ごとの基本情報を表示します。

SELECT
    [job].[job_id] AS '       '
   ,[job].[name] AS '    '
   ,[jstep].[step_uid] AS '       '
   ,[jstep].[step_id] AS '    '
   ,[jstep].[step_name] AS '    '
   ,CASE [jstep].[subsystem]
      WHEN 'ActiveScripting' THEN 'ActiveX Script'
      WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
      WHEN 'PowerShell' THEN 'PowerShell'
      WHEN 'Distribution' THEN 'Replication Distributor'
      WHEN 'Merge' THEN 'Replication Merge'
      WHEN 'QueueReader' THEN 'Replication Queue Reader'
      WHEN 'Snapshot' THEN 'Replication Snapshot'
      WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
      WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
      WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
      WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
      WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
      ELSE [jstep].[subsystem]
    END AS '      '
   ,CASE
        WHEN [px].[name] IS NULL THEN 'SQL SERVER      '
        ELSE [px].[name]
    END AS '      '
   ,[jstep].[database_name] AS '      '
   ,[jstep].[command] AS '    '
   ,CASE [jstep].[on_success_action]
      WHEN 1 THEN '         '
      WHEN 2 THEN '         '
      WHEN 3 THEN '     '
      WHEN 4
      THEN '    : '
           + QUOTENAME(CAST([jstep].[on_success_step_id] AS VARCHAR(3))) + ' '
           + [sOSSTP].[step_name]
    END AS '       '
   ,[jstep].[retry_attempts] AS '        '
   ,[jstep].[retry_interval] AS '        (  )'
   ,CASE [jstep].[on_fail_action]
      WHEN 1 THEN '         '
      WHEN 2 THEN '         '
      WHEN 3 THEN '     '
      WHEN 4
      THEN '    : '
           + QUOTENAME(CAST([jstep].[on_fail_step_id] AS VARCHAR(3))) + ' '
           + [sOFSTP].[step_name]
    END AS '       '
FROM [msdb].[dbo].[sysjobsteps] AS [jstep]
INNER JOIN [msdb].[dbo].[sysjobs] AS [job]
        ON [jstep].[job_id] = [job].[job_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [jstep].[job_id] = [sOSSTP].[job_id]
           AND [jstep].[on_success_step_id] = [sOSSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [jstep].[job_id] = [sOFSTP].[job_id]
           AND [jstep].[on_fail_step_id] = [sOFSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysproxies] AS [px]--      
        ON [jstep].[proxy_id] = [px].[proxy_id]
ORDER BY [job].[name], [jstep].[step_id]

作業手順ごとの実行状況を確認する

SELECT
    [job].[job_id] AS '       '
   ,[job].[name] AS '    '
   ,[jstep].[step_uid] AS '         '
   ,[jstep].[step_id] AS '    '
   ,[jstep].[step_name] AS '    '
   ,CASE [jstep].[last_run_outcome]
      WHEN 0 THEN '  '
      WHEN 1 THEN '  '
      WHEN 2 THEN '  '
      WHEN 3 THEN '  '
      WHEN 5 THEN '  '
    END AS '      '
   ,STUFF(STUFF(RIGHT('000000'
                      + CAST([jstep].[last_run_duration] AS VARCHAR(6)), 6), 3,
                0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)]
   ,[jstep].[last_run_retries] AS '          '
   ,CASE [jstep].[last_run_date]
      WHEN 0 THEN NULL
      ELSE CAST(CAST([jstep].[last_run_date] AS CHAR(8)) + ' '
           + STUFF(STUFF(RIGHT('000000'
                               + CAST([jstep].[last_run_time] AS VARCHAR(6)),
                               6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
    END AS '      '
FROM [msdb].[dbo].[sysjobsteps] AS [jstep]
INNER JOIN [msdb].[dbo].[sysjobs] AS [job]
        ON [jstep].[job_id] = [job].[job_id]
ORDER BY [job].[name], [jstep].[step_id]