利用SQL查看JOB执行的状况

最新推荐文章于 2025-01-17 17:56:10 发布

Reker熊

最新推荐文章于 2025-01-17 17:56:10 发布

阅读量4.5k

点赞数

分类专栏： SQL Server 文章标签： sql date join jobs integer each

本文链接：https://blog.youkuaiyun.com/tdl982324/article/details/120739

版权

SQL Server 专栏收录该内容

2 篇文章

订阅专栏

博客给出了两条SQL查询语句，用于查询失败或取消的作业信息。包括作业名称、步骤名称、状态、日期、时间和错误信息等。通过连接多个表，筛选出启用的作业，按日期降序排列，还提供了简化版查询语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT sj.name as job, left(sjh_d.step_name, 20) as [step name],

--What is it in English
CASE sjh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
ELSE 'Unknown'
END as status,

--Convert Integer date to regular datetime
SUBSTRING(CAST(sjh.run_date AS CHAR(8)),5,2) + '/' +
RIGHT(CAST(sjh.run_date AS CHAR(8)),2) + '/' +
LEFT(CAST(sjh.run_date AS CHAR(8)),4) as [date]

--Change run time into something you can recognize (hh:mm:ss)
, LEFT(RIGHT('000000' + CAST(sjh.run_time AS VARCHAR(10)),6),2) + ':' +
SUBSTRING(RIGHT('000000' + CAST(sjh.run_time AS VARCHAR(10)),6),3,2) + ':' +
RIGHT(RIGHT('000000' + CAST(sjh.run_time AS VARCHAR(10)),6),2) as [time]

--error message
,sjh_d.message as error

FROM msdb.dbo.sysjobs sj --job id and name

--Job history
INNER JOIN msdb.dbo.sysjobhistory sjh
ON sj.job_id = sjh.job_id
--fail details
LEFT JOIN msdb.dbo.sysjobhistory sjh_d
ON sjh.job_id = sjh_d.job_id AND sjh_d.step_id > 0

--Only enabled jobs
WHERE sj.enabled = 1
--Only job outcome not each step outcome
AND sjh.step_id = 0
--Only failed or cancelled jobs
AND (sjh.run_status = 0 OR sjh.run_status = 3)

--Latest date first
ORDER BY sjh.run_date DES

-------------------------------------------
简化版
-------------------------------------------
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],
b.message as Err_message
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
where a.enabled =1 and b.run_status <>1 and b.run_status <> 4
ORDER BY job, convert(char, b.run_date,111)+convert(char,b.run_time,111) desc