查看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语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
4月前
|
开发框架 .NET 数据库
asp.net企业费用报销管理信息系统VS开发sqlserver数据库web结构c#编程Microsoft Visual Studio
asp.net 企业费用报销管理信息系统是一套完善的web设计管理系统,系统具有完整的源代码和数据库,系统主要采用B/S模式开发。开发环境为vs2010,数据库为sqlserver2008,使 用c#语言开发 应用技术:asp.net c#+sqlserver 开发工具:vs2010 +sqlserver
33 0
|
7月前
|
SQL 存储 安全
docker 安装sqlserver数据库并开启代理(保姆级)
docker 安装sqlserver数据库并开启代理(保姆级)
434 0
|
11月前
|
开发框架 监控 前端开发
云LIS平台源码,基于B/S架构的实验室信息系统,技术架构:Asp.NET CORE 3.1 MVC + SQLserver + Redis
支持Westguard,Gubbuss+T(n)等多种质控规则,自动判断是否失控,可自动计算靶值、SD,多个质控品可列于一个图表上;每个质控品每天可多达7次结果,可使用平均值、最后一次结果,最好一次结果画图等;靶值可自动计算,免疫等支持按季度或者自定义日期画图
云LIS平台源码,基于B/S架构的实验室信息系统,技术架构:Asp.NET CORE 3.1 MVC + SQLserver + Redis
|
SQL 运维 Go
sql server 运维时CPU,内存,操作系统等信息查询(用sql语句)
原文:sql server 运维时CPU,内存,操作系统等信息查询(用sql语句) 我们只要用到数据库,一般会遇到数据库运维方面的事情,需要我们寻找原因,有很多是关乎处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统的,这时我们就需要查询他们的一些设置和内容,下面讲的就是如何查询它们的相关信息。
1061 0
|
SQL 索引 数据库
sql server 索引阐述系列八 统计信息
原文:sql server 索引阐述系列八 统计信息 一.概述     sql server在快速查询值时只有索引还不够,还需要知道操作要处理的数据量有多少,从而估算出复杂度,选择一个代价小的执行计划,这样sql server就知道了数据的分布情况。
956 0
|
SQL 缓存 数据库
SqlServer性能优化之获取缓存的查询计划中的聚合性能统计信息
SqlServer性能优化之获取缓存的查询计划中的聚合性能统计信息
4277 0
|
数据库
SqlServer 可更新订阅队列读取器代理错误:试图进行的插入或更新已失败
原文:SqlServer 可更新订阅队列读取器代理错误:试图进行的插入或更新已失败 今天发现队列读取器代理不停地尝试启动但总是出错: 其中内容如下: 队列读取器代理在连接“PublicationServer”上的“pubDB”时遇到错误“试图进行的插入或更新已失败, 原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION, 而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。
1393 0
|
存储 数据库
SqlServer 更改复制代理配置文件参数及两种冲突策略设置
原文:SqlServer 更改复制代理配置文件参数及两种冲突策略设置 由于经常需要同步测试并更改代理配置文件属性,所以总结成脚本,方便测试. 可更新订阅的冲突策略有两种情况:一是在发布中冲突,即订阅数据到发布时冲突;二是在订阅冲突,发布数据到订阅时冲突。
1371 0
|
数据库 存储
Sqlserver获取所有数据库名,表信息,字段信息,主键信息,以及表结构等。
原文:Sqlserver获取所有数据库名,表信息,字段信息,主键信息,以及表结构等。 --获取所有数据库名: SELECT name FROM master.
1191 0
|
数据库 数据安全/隐私保护 SQL
SqlServer批量压缩数据库日志-多数据库批量作业,批量备份还原
原文:SqlServer批量压缩数据库日志-多数据库批量作业,批量备份还原 --作业定时压缩脚本 多库批量操作 DECLARE @DatabaseName NVARCHAR(50) DEC...
1245 0