查詢作業基本信息和作業執行情況

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 '步驟名稱', CASEWHEN [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 NULLOR [jobh].[run_time] IS NULL THEN NULLELSE 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 NULLELSE 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 RowNumberFROM [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 '作業步驟類型',CASEWHEN [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 4THEN '轉到步驟: '+ 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 4THEN '轉到步驟: '+ 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 NULLELSE 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]

轉載于:https://www.cnblogs.com/xiongnanbin/p/b887dfff2957d1aee15f1d94e9957473.html

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的【原创】SQL SERVER 查询Job作业基本信息及执行情况的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。