主页 / dba / 问题 / 35979
Accepted
Roni Vered
Asked: 2013-03-06 05:58:20 +0800 CST

修复返回失败作业信息的查询

  • 772

我使用的是 SQL Server 2008 Sp3 环境。

我有一个查询，它返回有关过去 10 分钟内失败作业的信息。如果一个作业有两个调度程序（例如 - 一个作业在早上每 10 分钟运行一次，在一天的其余时间每 30 分钟运行一次），则查询将返回两行。您能帮我修复查询，以便它只返回所使用的相关调度程序的相关行吗？

我正在使用的查询粘贴在下面：

SELECT h.server
,j.name
,h.step_id
,h.step_name
,h.message
,h.run_status
,Rundatetime        = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
,h.run_duration
,s.last_outcome_message         
,sc.name AS ScheduleName
,s.last_run_time
,sjc.next_run_time
FROM msdb..sysjobhistory h 
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb..sysjobservers  s ON s.job_id=h.job_id
LEFT OUTER JOIN msdb..sysjobschedules  sjc ON sjc.job_id=h.job_id
LEFT OUTER JOIN msdb..sysschedules  sc ON sc.schedule_id=sjc.schedule_id
WHERE run_status=0
AND step_name<>'(Job outcome)'
--Looking on failed job from the last 10 minutes.
AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
ORDER BY h.run_date DESC ,h.run_time DESC

在此先感谢，罗尼。

sql-server sql-server-2008

3 个回答

  1. Best Answer

    这有点杂乱无章，但这是一个开始......

    我从 [last_outcome_message] 字段中提取了计划 ID，并将其加入到计划 ID。那应该给你正确的结果集

    我找不到任何其他地方存储 schedule_id 针对历史事件。

    SELECT h.server
,j.name
,h.step_id
,h.step_name
,h.message
,h.run_status
,Rundatetime        = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
,h.run_duration
,s.last_outcome_message         
,sc.name AS ScheduleName
,s.last_run_time
,sjc.next_run_time
FROM msdb..sysjobhistory h 
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb..sysjobservers  s ON s.job_id=h.job_id
LEFT OUTER JOIN msdb..sysjobschedules  sjc ON sjc.job_id=h.job_id

     AND CONVERT(INT, LTRIM(RTRIM(REPLACE(LEFT(last_outcome_message, 
                   PATINDEX('% (%', last_outcome_message)), 
                   'The job failed.  The Job was invoked by Schedule ', ''))))
          = sjc.schedule_id

LEFT OUTER JOIN msdb..sysschedules  sc ON sc.schedule_id=sjc.schedule_id
WHERE 
run_status=0
AND step_name<>'(Job outcome)'
--Looking on failed job from the last 10 minutes.
AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -10, GETDATE())
ORDER BY h.run_date DESC ,h.run_time DESC
    • 1

  2. 在 sjc.next_run_time 上做一分钟应该可以解决问题。

    • 0

  3. 更简单的方法不是寻找工作成果步骤吗？消息列将告诉您是什么调用了该作业。

    --AND step_name='(Job outcome)'

SELECT h.server
,j.name
,h.step_id
,h.step_name
,h.message
,h.run_status
,Rundatetime        = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
,h.run_duration
FROM msdb..sysjobhistory h 
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
INNER JOIN msdb..sysjobservers  s ON s.job_id=h.job_id
WHERE run_status=0
AND step_name='(Job outcome)'
--Looking on failed job from the last 10 minutes.
AND CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(mi, -100, GETDATE())
ORDER BY h.run_date DESC ,h.run_time DESC
    • 0

相关问题