从 msdb.dbo.sysjobhistory 获取最近 24 小时的作业记录

发布于 2024-09-26 22:06:35 字数 160 浏览 3 评论 0原文

我想编写一个查询来从“msdb.dbo.sysjobhistory”表中获取过去 24 小时的作业记录，但我无法获取，因为我得到的“run_date”和“run_time”列以数字形式返回。如何将“run_date”和“run_time”列转换为日期时间变量，并使用它来获取最近 24 小时的作业历史记录？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浮生未歇 2024-10-03 22:06:35

查看这篇文章 - 它展示了如何从 sysjobhistory 中“解码”那些 run_date 列。

您应该能够通过如下查询获取过去 24 小时内的条目：

SELECT 
    j.name as JobName, 
    LastRunDateTime = 
    CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' 
    + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
FROM 
    msdb..sysjobs j
INNER JOIN
    msdb..sysjobhistory jh ON j.job_id = jh.job_id
WHERE
    CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' 
    + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(HOUR, -24, GETDATE())

Check out this post - it shows how to "decode" those run_date columns from sysjobhistory.

You should be able to get the entries from the last 24 hours with a query something like this:

SELECT 
    j.name as JobName, 
    LastRunDateTime = 
    CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' 
    + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
FROM 
    msdb..sysjobs j
INNER JOIN
    msdb..sysjobhistory jh ON j.job_id = jh.job_id
WHERE
    CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' 
    + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(HOUR, -24, GETDATE())

回复收藏 0 原文

不疑不惑不回忆 2024-10-03 22:06:35

对于 2000 之后的数据库，您可以调用 msdb 数据库中的一个函数来返回日期时间：

msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime'

如果您使用的是 sql 2000，您可以从更高版本复制该函数的源代码并在 2000 的实例中创建它。希望我能将这一切归功于我，但我最初在这里找到它： mssqltips.com

For databases after 2000, there is a function in the msdb database you can call that will return datetime:

msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime'

If you are using sql 2000, you can copy the source of that function from a later version and create it in your instance of 2000. I wish I could take credit for all of this, but I originally found it here: mssqltips.com

回复收藏 0 原文

暖风昔人 2024-10-03 22:06:35

我的一位同事指出，其他给定的解决方案只能找到我们在 24 小时前（或更短时间）开始的作业，而不是 24 小时完成的作业之前（或更短）。他提出了以下内容来定位已完成的工作：

SELECT j.name AS JobName
    ,LastCompletedDateTime = DATEADD(day, (run_duration / 240000), CONVERT(DATETIME, msdb.dbo.agent_datetime(run_date, run_time))) + 
        STUFF(STUFF(REPLACE(STR((run_duration % 240000), 7, 0), ' ', '0'), 4, 0, ':'), 7, 0, ':')
FROM msdb..sysjobs j
INNER JOIN msdb..sysjobhistory jh ON j.job_id = jh.job_id
WHERE DATEADD(day, (run_duration / 240000), CONVERT(DATETIME, msdb.dbo.agent_datetime(run_date, run_time))) + 
    STUFF(STUFF(REPLACE(STR((run_duration % 240000), 7, 0), ' ', '0'), 4, 0, ':'), 7, 0, ':') > DATEADD(HOUR, - 24, GETDATE())

我相信其中一部分来自博客，但我们无法找到它，当我找到它时，我也会链接它......

A co-worker of mine pointed out that the other given solutions only find jobs that we're started 24 hours ago (or less), not jobs that were completed 24 hours ago (or less). He proposed something along the lines of the following to location completed jobs:

SELECT j.name AS JobName
    ,LastCompletedDateTime = DATEADD(day, (run_duration / 240000), CONVERT(DATETIME, msdb.dbo.agent_datetime(run_date, run_time))) + 
        STUFF(STUFF(REPLACE(STR((run_duration % 240000), 7, 0), ' ', '0'), 4, 0, ':'), 7, 0, ':')
FROM msdb..sysjobs j
INNER JOIN msdb..sysjobhistory jh ON j.job_id = jh.job_id
WHERE DATEADD(day, (run_duration / 240000), CONVERT(DATETIME, msdb.dbo.agent_datetime(run_date, run_time))) + 
    STUFF(STUFF(REPLACE(STR((run_duration % 240000), 7, 0), ' ', '0'), 4, 0, ':'), 7, 0, ':') > DATEADD(HOUR, - 24, GETDATE())

I believe part of this comes from a blog, but we cannot locate it, when I do I'll link that as well...

回复收藏 0 原文

~没有更多了~