SQLSERVER查询作业（Job）基本信息及执⾏情况 ⾸先SqlServer的作业(job)是定期执⾏某个语句或者存储过程的任务，类似于windows⾥⾯的执⾏计划。
作业是在SqlServer代理⾥⾯，如下⾯的截图:
关于具体如何创建作业,⽹上资料太多，这⾥不再概述。 下⾯说的是查询所有作业的sql
查询作业基本信息和作业执⾏情况
SELECT
[jop].[job_id] 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 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 '作业名称'
,[jobstep].[step_uid] AS '步骤唯⼀标识符'
,[jobstep].[step_id] AS '步骤编号'
,
[jobstep].[step_name] AS '步骤名称'
,CASE [jobstep].[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 [jobstep].[subsystem]
END AS '作业步骤类型'
,CASE
WHEN [px].[name] IS NULL THEN 'SQL SERVER代理服务账户'
ELSE [px].[name]
END AS '步骤运⾏账户'
,[jobstep].[database_name] AS '执⾏数据库名'
,[jobstep].[command] AS '执⾏命令'
,CASE [jobstep].[on_success_action]
WHEN 1 THEN '退出报表成功的作业'
WHEN 2 THEN '退出报告失败的作业'
WHEN 3 THEN '转到下⼀步'
WHEN 4
THEN '转到步骤: '
+ QUOTENAME(CAST([jobstep].[on_success_step_id] AS VARCHAR(3))) + ' ' + [sOSSTP].[step_name]
END AS '执⾏成功后操作'
,[jobstep].[retry_attempts] AS '失败时的重试次数'
,[jobstep].[retry_interval] AS '重试间的等待时间(分钟)'
,CASE [jobstep].[on_fail_action]
建模仿真WHEN 1 THEN '退出报告成功的作业'
WHEN 2 THEN '退出报告失败的作业'
WHEN 3 THEN '转到下⼀步'
WHEN 4
THEN '转到步骤: '
+ QUOTENAME(CAST([jobstep].[on_fail_step_id] AS VARCHAR(3))) + ' '
+ [sOFSTP].[step_name]
END AS '执⾏失败后操作'
水上步行器
FROM [msdb].[dbo].[sysjobsteps] AS [jobstep]
INNER JOIN [msdb].[dbo].[sysjobs] AS [job]
ON [jobstep].[job_id] = [job].[job_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
ON [jobstep].[job_id] = [sOSSTP].[job_id]
AND [jobstep].[on_success_step_id] = [sOSSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
ON [jobstep].[job_id] = [sOFSTP].[job_id]
AND [jobstep].[on_fail_step_id] = [sOFSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysproxies] AS [px]--代理账户信息
ON [jobstep].[proxy_id] = [px].[proxy_id]
WHERE [jobstep].[database_name]='数据库的名称'
AND UPPER([jobstep].[command]) LIKE UPPER('%执⾏的命令名称%') ORDER BY [job].[name], [jobstep].[step_id]
查看每个作业步骤基本信息
SELECT
[job].[job_id] AS '作业唯⼀标识符'
,[job].[name] AS '作业名称'
,[jobstep].[step_uid] AS '作业步骤唯⼀标识符'
,[jobstep].[step_id] AS '步骤编号'oled tft
,[jobstep].[step_name] AS '步骤名称'
,CASE [jobstep].[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([jobstep].[last_run_duration] AS VARCHAR(6)), 6), 3,
0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)]
,[jobstep].[last_run_retries] AS '上次运⾏重复执⾏次数'
,CASE [jobstep].[last_run_date]
WHEN 0 THEN NULL
ELSE CAST(CAST([jobstep].[last_run_date] AS CHAR(8)) + ' '
+ STUFF(STUFF(RIGHT('000000'+ CAST([jobstep].[last_run_time] AS VARCHAR(6)),6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
END AS '上次运⾏时间'
FROM [msdb].[dbo].[sysjobsteps] AS [jobstep]
INNER JOIN [msdb].[dbo].[sysjobs] AS [job]
ON [jobstep].[job_id] = [job].[job_id]
WHERE [jobstep].[database_name]='数据库的名称'
AND UPPER([jobstep].[command]) LIKE UPPER('%执⾏的命令名称%')
ORDER BY [job].[name], [jobstep].[step_id]
查询作业执⾏结果语句
SELECT a.name as job, left(b.step_name, 20) as [step name],
CASE b.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' ELSE 'Running' END as status,
SUBSTRING(CAST(b.run_date AS CHAR(8)),5,2) + '/' + RIGHT(CAST(b.run_date AS CHAR(8)),2) + '/' +
LEFT(CAST(b.run_date AS CHAR(8)),4) as [date],
LEFT(RIGHT('000000' + CAST(b.run_time AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(b.run_time AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(b.run_time AS VARCHAR(10)),6),2) as [time],
From msdb..sysjobs a
INNER JOIN msdb..sysjobhistory b ON a.job_id = b.job_id
inner join (select job_id,max(instance_id) as maxinstance from msdb..sysjobhistory group by job_id) x
on a.job_id = x.job_id and b.instance_id = x.maxinstance
abled =1 and a.name='作业名称'
屋面玻纤瓦
ORDER BY job, convert(char, b.run_date,111)+convert(char,b.run_time,111) desc
>煮面炉
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议