搜索
首页数据库mysql教程4.SQLServer数据库状态监控-作业状态

有很多地方可以设置定时任务,比如:Windows的计划任务,Linux下的crontab,各种开发工具里的timer组件。SQL Server也有它的定时任务组件 SQL Server Agent,基于它可以方便的部署各种数据库相关的作业(job)。 一 . 作业历史纪录 作业的历史纪录按时间采用FI

有很多地方可以设置定时任务,比如:Windows的计划任务,Linux下的crontab,各种开发工具里的timer组件。SQL Server也有它的定时任务组件 SQL Server Agent,基于它可以方便的部署各种数据库相关的作业(job)。

作业历史纪录

作业的历史纪录按时间采用FIFO原则,当累积的作业历史纪录达到上限时,就会删除最老的纪录。

1. 作业历史纪录数配置

所有作业总计纪录条数默认为1000,最多为999999条;单个作业总计记录条数默认为100,最多为999999条。有下面2种方式可以进行修改:

(1) SSMS/SQL Server Agent/属性/历史;

(2) 未记载的扩展存储过程,SQL Server 2005及以后版本适用,以下脚本将记录数设回默认值:

EXEC msdb.dbo.sp_set_sqlagent_properties 
 @jobhistory_max_rows=-1,
 @jobhistory_max_rows_per_job=-1
GO


2. 删除作业历史纪录

(1) SSMS/SQL Server Agent/右击作业文件夹或某个作业/查看历史纪录/清除

在SQL Server 2000中会一次清除所有作业历史记录,SQL Server 2005 及以后版本可以有选择的清除某个作业/某个时间之前的历史纪录;

(2) SQL Server 2005及以后版本,提供了系统存储过程如下:

--清除所有作业15天前的纪录
DECLARE @OldestDate datetime
SET @OldestDate = GETDATE()-15
EXEC msdb.dbo.sp_purge_jobhistory 
@oldest_date=@OldestDate

--清除作业”Test”3天前的纪录
DECLARE @OldestDate datetime
DECLARE @JobName varchar(256)
SET @OldestDate = GETDATE()-3
SET @JobName = 'Test'
EXEC msdb.dbo.sp_purge_jobhistory 
@job_name=@JobName, 
@oldest_date=@OldestDate

作业历史纪录数有上限,通常不需要手动去删除。

 

3. 保留作业历史纪录

即便设置了历史记录上限到999999,如果作业很多,加之作业运行很频繁,最终历史记录还是会被慢慢删除掉。

如果想要保留某些作业历史的记录,可以打开作业属性/步骤/编辑/高级,选择将这个步骤的历史记录输出到文件/自定义表中,如下图:

 

作业运行状态

界面上可以通过: SSMS/SQL Server Agent/右击作业文件夹或某个作业/查看历史纪录,如下用SQL 语句检查作业状态。

1. 作业上次运行状态及时长

利用系统表msdb.dbo.sysjobhistory:

(1) 表中的run_status字段表示作业上次运行状态,有0~3共4种状态值,详见帮助文档,另外在2005的帮助文档中写到:sysjobhistory的run_status为4表示运行中,经测试是错误的,在2008的帮助中已没有4这个状态;

(2) 表中run_duration字段表示作业上次运行时长,格式为HHMMSS,比如20000则表示运行了2小时。

如下脚本查看所有作业最后一次运行状态及时长:

if OBJECT_ID('tempdb..#tmp_job') is not null
    drop table #tmp_job
 
--只取最后一次结果
select job_id,
       run_status,
       CONVERT(varchar(20),run_date) run_date,
       CONVERT(varchar(20),run_time) run_time,
       CONVERT(varchar(20),run_duration)run_duration
  into #tmp_job
  from msdb.dbo.sysjobhistoryjh1
 where jh1.step_id = 0
   and(select COUNT(1) from msdb.dbo.sysjobhistory jh2 
        wherejh2.step_id = 0
          and(jh1.job_id = jh2.job_id)
          and(jh1.instance_id <=jh2.instance_id))=1
 
--排除syspolicy_purge_history这个系统作业
select a.namejob_name,
       case b.run_status when 0 then 'Failed'
                         when 1 then 'Succeeded'
                         when 2 then 'Retry'
                         when 3 then 'Canceled'
       else 'Unknown' 
       end as job_status,
       LEFT(run_date,4)+'-'+SUBSTRING(run_date,5,2)+'-'+RIGHT(run_date,2)
       +SPACE(1)
       +LEFT(RIGHT(1000000+run_time,6),2)+':'
           +SUBSTRING(RIGHT(1000000+run_time,6),3,2)+':'
           +RIGHT(RIGHT(1000000+run_time,6),2) as job_started_time,
       +LEFT(RIGHT(1000000+run_duration,6),2)+':'
           +SUBSTRING(RIGHT(1000000+run_duration,6),3,2)+':'
           +RIGHT(RIGHT(1000000+run_duration,6),2) as job_duration
  from msdb.dbo.sysjobs a 
  left join    #tmp_jobb 
    on a.job_id=b.job_id 
 where a.name not in('syspolicy_purge_history')
   and a.enabled = 1
 order by b.run_status asc,a.name,b.run_duration desc


2. 作业当前运行状态及时长

什么时候可能要检查作业的当前状态?

(1) 需要关闭SQL Server或SQL Server Agent服务时;

(2) 等到当前作业完成,有后续动作;

(3) 纯粹只是查看当前作业运行到哪个步骤等等。

 

通过SSMS/SQL Server Agent/右击作业文件夹或某个作业/查看历史纪录,看到的作业历史记录存放在:

select * from msdb.dbo.sysjobhistory

需要注意的是:至少作业已完成第一步运行,sysjobhistory表中才会有作业历史纪录,若当前作业没有完成任何一个步骤,那表里就不会有本次运行纪录。所以作业当前状态用有时无法通过sysjobhistory查看,尤其是作业只有1个步骤且运行时间很长时。

 

2.1. SQL Server 2005及以后版本

(1) 当前运行状态:系统存储过程msdb.dbo.sp_help_job,返回所有作业的运行状态(current_execution_status),共7种状态值,详见帮助文档。查看所有作业状态如下:

exec msdb..sp_help_job

(2) 当前运行时长:系统存储过程sp_help_job无法获得作业运行时长,可通过新增的系统表sysjobactivity来查看。查看正在运行的作业如下:

select a.name,
       b.start_execution_date,
       DATEDIFF(MI,b.start_execution_date,GETDATE()) as job_duration
  from msdb..sysjobs a
 inner join msdb..sysjobactivityb
    on a.job_id = b.job_id
 where b.start_execution_date isnot null
   and b.stop_execution_date isnull


以下脚本结合sp_help_job和sysjobactivity,得到作业的当前状态及时长:

exec sp_configure'show advanced options',1
RECONFIGURE
exec sp_configure'Ad Hoc Distributed Queries',1
RECONFIGURE
 
if OBJECT_ID('tempdb..#jobinfo') is not null
    drop table #jobinfo
    
select * into #jobinfo
from openrowset('sqloledb', 'server=(local);trusted_connection=yes','exec msdb.dbo.sp_help_job')
 
select a.name,
       j.current_execution_status,
       b.start_execution_date,
       DATEDIFF(MI,b.start_execution_date,GETDATE()) asjob_duration_minute
  from msdb..sysjobs a
 inner join msdb..sysjobactivityb
    on a.job_id = b.job_id
 inner join #jobinfo j
    on a.job_id = j.job_id   
 where b.start_execution_date isnot null
   and b.stop_execution_date isnull


2.2. SQL Server 2000沿用过来的方法

在SQL Server 2000时,没有sysjobactivity这个系统表,通常借助sysprocesses监视作业的当前运行状态及时长。

select j.name, 
       p.status ascurrent_execution_status, 
       p.last_batchas start_execution_date,
       ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0) asjob_duration_minute
  from msdb.dbo.sysjobs j, master..sysprocesses p
 where p.program_name like 'SQLAgent - TSQL JobStep(Job%'
   and substring((cast(j.job_id as varchar(36))),7,2) +
       substring((cast(j.job_id as varchar(36))),5,2) +
       substring((cast(j.job_id as varchar(36))),3,2) +
       substring((cast(j.job_id as varchar(36))),1,2) +
       substring((cast(j.job_id as varchar(36))),12,2) +
       substring((cast(j.job_id as varchar(36))),10,2) +
       substring((cast(j.job_id as varchar(36))),17,2) +
       substring((cast(j.job_id as varchar(36))),15,2) +
       substring((cast(j.job_id as varchar(36))),20,4) +
       substring((cast(j.job_id as varchar(36))),25,12) 
           = substring((cast(p.program_name as varchar(75))),32,32)


sysprocesses里获得的作业编号跟sysjobs里是不一致的,所以上面进行了转换,通常只转换job_id的前8位字符也行,如下脚本做了job_id的简化转换,并检查作业已运行超过30分钟:

declare @MaxMinutes int
    set @MaxMinutes = 30 
 
select j.name, 
       p.status ascurrent_execution_status, 
       p.last_batchas start_execution_date,
       ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0) asjob_duration_minute
  from msdb..sysjobs j
 inner join master..sysprocesses p
    on substring(left(cast(j.job_id as varchar(36)),8),7,2) +
       substring(left(cast(j.job_id as varchar(36)),8),5,2) +
       substring(left(cast(j.job_id as varchar(36)),8),3,2) +
       substring(left(cast(j.job_id as varchar(36)),8),1,2) = substring(p.program_name,32,8)
 where p.program_name like 'SQLAgent - TSQL JobStep(Job%'
  and ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0) > @MaxMinutes


还有种比较笨的方法,在要监视的所有作业中增加一个步骤,如 : select GETDATE() 放在第一步,这样在sysjobhistory中就会有步骤1的运行纪录了,以此为起点,可以计算已运行时长。如果有很多已经部署的job,这确实不是个好办法。

又或者,在每个作业最后一步,放一个检查的步骤,这样所有状态时长全都监视到了,问题是如果作业运行时间过长,最后的检查步骤根本无法被运行到。

 

作业状态告警

作业在完成后,自己有状态检查和告警机制,通常选择邮件告警,如下图:

但这仅限对作业最终运行状态监视:

(1) 没有运行结束的作业无法告警,或者说对作业的运行时长没有监视;

(2) 如果作业在某个中间步骤设置了:失败后继续下一步,后续的作业步骤都成功,那么作业最终状态不会显示会失败,不会触发告警,如下脚本检查每个作业的所有步骤最后一次运行状态:

if OBJECT_ID('tempdb..#tmp_job_step') is not null
    drop table #tmp_job_step
 
select jh1.job_id,
       jh1.step_id,
       jh1.run_status,
       CONVERT(varchar(20),jh1.run_date) run_date,
       CONVERT(varchar(20),jh1.run_time) run_time,
       CONVERT(varchar(20),jh1.run_duration) run_duration
  into #tmp_job_step
  from msdb.dbo.sysjobhistoryjh1
 where (select COUNT(1) from msdb.dbo.sysjobhistoryjh2 
        where (jh1.job_id = jh2.job_id and jh1.step_id = jh2.step_id)
          and(jh1.instance_id <=jh2.instance_id))=1
 
select a.namejob_name,
       s.step_name,
       case b.run_status when 0 then 'Failed'
                         when 1 then 'Succeeded'
                         when 2 then 'Retry'
                         when 3 then 'Canceled'
       else 'Unknown' 
       end as job_status,
       LEFT(run_date,4)+'-'+SUBSTRING(run_date,5,2)+'-'+RIGHT(run_date,2)
       +SPACE(1)
       +LEFT(RIGHT(1000000+run_time,6),2)+':'
           +SUBSTRING(RIGHT(1000000+run_time,6),3,2)+':'
           +RIGHT(RIGHT(1000000+run_time,6),2) as job_started_time,
       +LEFT(RIGHT(1000000+run_duration,6),2)+':'
           +SUBSTRING(RIGHT(1000000+run_duration,6),3,2)+':'
           +RIGHT(RIGHT(1000000+run_duration,6),2) as job_duration
  from msdb.dbo.sysjobs a 
  left join    #tmp_job_stepb 
    on a.job_id=b.job_id
 inner join msdb.dbo.sysjobsteps s
    on b.job_id = s.job_id and b.step_id = s.step_id
 where a.name not in('syspolicy_purge_history')
   and a.enabled = 1
 order by b.run_status asc,a.name,b.run_duration desc


小结

SQL Server Agent作业自身的告警机制,有时并不够用,所以还需要部署另外的作业,来检查其他所有作业的运行状况,大致步骤如下 :

(1) 部署数据库邮件;

(2) 部署作业:定时检查其他所有作业/步骤状态,发邮件告警;

作业运行时长可以在这一并检查,有时一些作业运行了很多天没结束还没人知道,也可以考虑放在性能监控里,和其他数据库请求一起监控。但是对于时长,通常需要有个性能基线,如果没有的话直接和历史最大值相比也是不错的选择。


声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
MySQL与Sqlite有何不同?MySQL与Sqlite有何不同?Apr 24, 2025 am 12:12 AM

MySQL和SQLite的主要区别在于设计理念和使用场景:1.MySQL适用于大型应用和企业级解决方案,支持高性能和高并发;2.SQLite适合移动应用和桌面软件,轻量级且易于嵌入。

MySQL中的索引是什么?它们如何提高性能?MySQL中的索引是什么?它们如何提高性能?Apr 24, 2025 am 12:09 AM

MySQL中的索引是数据库表中一列或多列的有序结构,用于加速数据检索。1)索引通过减少扫描数据量提升查询速度。2)B-Tree索引利用平衡树结构,适合范围查询和排序。3)创建索引使用CREATEINDEX语句,如CREATEINDEXidx_customer_idONorders(customer_id)。4)复合索引可优化多列查询,如CREATEINDEXidx_customer_orderONorders(customer_id,order_date)。5)使用EXPLAIN分析查询计划,避

说明如何使用MySQL中的交易来确保数据一致性。说明如何使用MySQL中的交易来确保数据一致性。Apr 24, 2025 am 12:09 AM

在MySQL中使用事务可以确保数据一致性。1)通过STARTTRANSACTION开始事务,执行SQL操作后用COMMIT提交或ROLLBACK回滚。2)使用SAVEPOINT可以设置保存点,允许部分回滚。3)性能优化建议包括缩短事务时间、避免大规模查询和合理使用隔离级别。

在哪些情况下,您可以选择PostgreSQL而不是MySQL?在哪些情况下,您可以选择PostgreSQL而不是MySQL?Apr 24, 2025 am 12:07 AM

选择PostgreSQL而非MySQL的场景包括:1)需要复杂查询和高级SQL功能,2)要求严格的数据完整性和ACID遵从性,3)需要高级空间功能,4)处理大数据集时需要高性能。PostgreSQL在这些方面表现出色,适合需要复杂数据处理和高数据完整性的项目。

如何保护MySQL数据库?如何保护MySQL数据库?Apr 24, 2025 am 12:04 AM

MySQL数据库的安全可以通过以下措施实现:1.用户权限管理:通过CREATEUSER和GRANT命令严格控制访问权限。2.加密传输:配置SSL/TLS确保数据传输安全。3.数据库备份和恢复:使用mysqldump或mysqlpump定期备份数据。4.高级安全策略:使用防火墙限制访问,并启用审计日志记录操作。5.性能优化与最佳实践:通过索引和查询优化以及定期维护兼顾安全和性能。

您可以使用哪些工具来监视MySQL性能?您可以使用哪些工具来监视MySQL性能?Apr 23, 2025 am 12:21 AM

如何有效监控MySQL性能?使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。1.使用mysqladmin查看连接数。2.用SHOWGLOBALSTATUS查看查询数。3.PMM提供详细性能数据和图形化界面。4.MySQLEnterpriseMonitor提供丰富的监控功能和报警机制。

MySQL与SQL Server有何不同?MySQL与SQL Server有何不同?Apr 23, 2025 am 12:20 AM

MySQL和SQLServer的区别在于:1)MySQL是开源的,适用于Web和嵌入式系统,2)SQLServer是微软的商业产品,适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显着差异,选择时需考虑项目规模和未来扩展性。

在哪些情况下,您可以选择SQL Server而不是MySQL?在哪些情况下,您可以选择SQL Server而不是MySQL?Apr 23, 2025 am 12:20 AM

在需要高可用性、高级安全性和良好集成性的企业级应用场景下,应选择SQLServer而不是MySQL。1)SQLServer提供企业级功能,如高可用性和高级安全性。2)它与微软生态系统如VisualStudio和PowerBI紧密集成。3)SQLServer在性能优化方面表现出色,支持内存优化表和列存储索引。

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热工具

螳螂BT

螳螂BT

Mantis是一个易于部署的基于Web的缺陷跟踪工具,用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。

EditPlus 中文破解版

EditPlus 中文破解版

体积小,语法高亮,不支持代码提示功能

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

功能强大的PHP集成开发环境

安全考试浏览器

安全考试浏览器

Safe Exam Browser是一个安全的浏览器环境,用于安全地进行在线考试。该软件将任何计算机变成一个安全的工作站。它控制对任何实用工具的访问,并防止学生使用未经授权的资源。

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)