• 欢迎访问金笔头博客,这是一个菜鸟(伪)程序员的自留地,欢迎访问我的github:点击进入

SQL SERVER 查询Job基本信息和执行情况

SQLServer eason 1354次浏览 0个评论 扫描二维码

1.查询作业基本信息

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]

2.查询作业最后执行情况

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]

3.查询作业和步骤的详细信息

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]

4.查询每个作业步骤的执行情况

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]

金笔头博客, 版权所有丨如未注明 , 均为原创, 转载请注明SQL SERVER 查询Job基本信息和执行情况
喜欢 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址