查看SQLServer 代理作业的历史信息-阿里云开发者社区

开发者社区> 杰克.陈> 正文

查看SQLServer 代理作业的历史信息

简介: 原文: 查看SQLServer 代理作业的历史信息 不敢说众所周知,但是大部分人都应该知道SQLServer的代理作业情况都存储在SQLServer5大系统数据库(master/msdb/model/tempdb/resources)中的MSDB中,而由于代理作业的长期运行和种类较多,所以一般可以看到msdb的大小往往比其他库加起来还大。
+关注继续查看
原文: 查看SQLServer 代理作业的历史信息

不敢说众所周知,但是大部分人都应该知道SQLServer的代理作业情况都存储在SQLServer5大系统数据库(master/msdb/model/tempdb/resources)中的MSDB中,而由于代理作业的长期运行和种类较多,所以一般可以看到msdb的大小往往比其他库加起来还大。本文主要专注在如何查询作业的运行时间点及运行持续时间上。

作为DBA,周期性检查作业情况是一下非常重要的任务。本文不讲述太深入。只讲述如何查询作业的历史运行情况。并加入一下在联机丛书上没有提及,也就是所谓的未公开的系统函数。

作业执行的历史信息存放在msdb.dbo.sysjobhistory中。但是在这个表里面,日期和时间列的显式方式会有点不常规,这就引出了本文的意图。首先我们来看看表里的数据,这里需要关联一下sysjobs表:


SELECT  j.name AS 'JobName' ,
          run_date ,
          run_time
  FROM    msdb.dbo.sysjobs j
          INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
  WHERE   j.enabled = 1  --Only Enabled Jobs
  ORDER BY JobName ,
          run_date ,
          run_time DESC

运行上面的代码,得到以下的结果:


可以看到run_date这列,虽然能看得懂,但是是YYYYMMDD这样的格式,用起来可能有点不方便。而run_time就更加难用了。Run_time中的180002意味着:18:00:02执行。这些不直观的数据对时常需要使用的DBA来说是一种痛苦,当然,可以通过字符串函数来转换成自己喜欢看的格式。但是这里提供一个微软未公开的函数:

MSDB.dbo.agent_datetime(run_date,run_time)

它会返回一个比较常规的日期格式,使得使用和查看的时候都很方便,作为一个未公开的函数,对其的了解不多只需要会用就可以了。可以使用下面的例子:


SELECT  j.name AS 'JobName' ,
         run_date ,
         run_time ,
         msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime'
 FROM    msdb.dbo.sysjobs j
         INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
 WHERE   j.enabled = 1  --Only Enabled Jobs
 ORDER BY JobName ,
         RunDateTime DESC
 

结果转换后,得到下面的结果:


可以看到经过函数格式化之后,数据已经很直观了。特别注意,这个未公开函数是从2005以后才引入,2000是没有的。只能通过字符串处理来获得同样的效果。

现在再来看看另外一列,run_duration,运行持续时间,同样,这列是int类型,也和run_time一样,不直观。

SELECT  j.name AS 'JobName' ,
         run_date ,
         run_time ,
         msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime' ,
         run_duration
 FROM    msdb.dbo.sysjobs j
         INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
 WHERE   j.enabled = 1  --Only Enabled Jobs
 ORDER BY JobName ,
         RunDateTime DESC
 

结果如下:



这列两位数代表仅仅是秒,3位数代表秒和分。单纯从这里比较难看出作业的运行时间。对分析不利。比较遗憾的是没有另外的存储过程来转换这列,所以需要自己编写代码,可以用下面的代码来转换:


SELECT  j.name AS 'JobName' ,
         run_date ,
         run_time ,
         msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime' ,
         run_duration ,
         ( ( run_duration / 10000 * 3600 + ( run_duration / 100 ) % 100 * 60
             + run_duration % 100 + 31 ) / 60 ) AS 'RunDurationMinutes'
 FROM    msdb.dbo.sysjobs j
         INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
 WHERE   j.enabled = 1  --Only Enabled Jobs
 ORDER BY JobName ,
         RunDateTime DESC
 
为了方便展示,这里我筛选了持续时间比较长的几个作业。



 

对于很多ETL的作业,可能会有很多步骤,下面来把这些步骤也带出来,这就要关联另外一个表msdb.dbo.sysjobsteps


SELECT  j.name AS 'JobName' ,
         s.step_id AS 'Step' ,
         s.step_name AS 'StepName' ,
         msdb.dbo.agent_datetime(run_date, run_time) AS 'RunDateTime' ,
         ( ( run_duration / 10000 * 3600 + ( run_duration / 100 ) % 100 * 60
             + run_duration % 100 + 31 ) / 60 ) AS 'RunDurationMinutes'
 FROM    msdb.dbo.sysjobs j
         INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
         INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id
                                                AND s.step_id = h.step_id
                                                AND h.step_id <> 0
 WHERE   j.enabled = 1   --Only Enabled Jobs
 ORDER BY JobName ,
         RunDateTime DESC
 



通过这个查询,可以检查到具体哪个作业运行时间最长,然后进行检查和优化。对于SQLServer 代理作业还有很多事情要做,由于主题原因,也不可能一篇就全部说完,将在后续文章中说明。

从代理作业中检查性能问题只是查询性能问题及检查数据库运行情况的手段之一,很多数据库管理方面的操作其实往往不是单一的,而是一系列的操作合成的。但是学会一种工具,你就多了一样利器。



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
使用SQL Server 2005作业设置定时任务
原文:使用SQL Server 2005作业设置定时任务 公司有一个老项目由于直接把终端拍摄的图片以二进制的形式保存到数据库中,数据库比较大所以需要经常删除这些冗余数据,手动删除费时费力,项目组长让我把这些操作变成自动的,每天执行一次,只保留最近两个月的图片数据。
638 0
VisualSVN Server仓库迁移到Linux(包含所有版本, 权限,用户信息)
公司开发服务器从Windows换成CentOS,所以要把原服务都转移到Linux下,MySQL、SMB的迁移都很顺利,但是SVN的转移却遇到了些问题,花费了三天时间,走了不少弯路,现在总算解决了SVN迁移问题,记录下: 在Windows上我们是用VisualSVN Server作SVN服务的,Linux上是编译安装的subversion 1.7.7。
1531 0
PolarDB-X 1.0-SQL 手册-函数-信息函数
信息函数用于返回动态的数据库信息。DRDS 支持如下信息函数:
12 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载