4. SQL Server数据库状态监控 - 作业状态

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
简介: 原文:4. SQL Server数据库状态监控 - 作业状态有很多地方可以设置定时任务,比如:Windows的计划任务,Linux下的crontab,各种开发工具里的timer组件。SQL Server也有它的定时任务组件 SQL Server Agent,基于它可以方便的部署各种数据库相关的作业(job)。
原文: 4. SQL Server数据库状态监控 - 作业状态

有很多地方可以设置定时任务,比如: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.sysjobhistory jh1
 where jh1.step_id = 0
   and (select COUNT(1) from msdb.dbo.sysjobhistory jh2 
        where jh2.step_id = 0 
          and (jh1.job_id = jh2.job_id)
          and (jh1.instance_id <= jh2.instance_id))=1

--排除syspolicy_purge_history这个系统作业
select a.name job_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 b 
    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..sysjobactivity b
    on a.job_id = b.job_id
 where b.start_execution_date is not null
   and b.stop_execution_date is null 

 

以下脚本结合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()) as job_duration_minute
  from msdb..sysjobs a
 inner join msdb..sysjobactivity b
    on a.job_id = b.job_id
 inner join #jobinfo j
    on a.job_id = j.job_id    
 where b.start_execution_date is not null
   and b.stop_execution_date is null 

 

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

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

select j.name, 
       p.status as current_execution_status, 
       p.last_batch as start_execution_date,
       ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0) as job_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 as current_execution_status, 
       p.last_batch as start_execution_date,
       ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0) as job_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.sysjobhistory jh1
 where (select COUNT(1) from msdb.dbo.sysjobhistory jh2 
        where (jh1.job_id = jh2.job_id and jh1.step_id = jh2.step_id)
          and (jh1.instance_id <= jh2.instance_id))=1

select a.name job_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_step b 
    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) 部署作业:定时检查其他所有作业/步骤状态,发邮件告警;

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

 

相关实践学习
使用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
目录
相关文章
|
3天前
|
存储 监控 数据库
局域网监控软件中的Transact-SQL事务管理
**T-SQL事务管理**确保数据的原子性、一致性、隔离性和持久性。使用`BEGIN TRANSACTION`, `COMMIT`和`ROLLBACK`来控制事务。例如,在更新账户余额的事务中,如果所有操作成功则提交,否则回滚。错误处理通过`TRY-CATCH`结构实现,嵌套事务允许在事务内开启新的事务。通过触发器和存储过程,如`DataChangeTrigger`和`SubmitDataToWebsite`,可以自动化数据提交到网站,当表数据变动时触发。
19 5
|
1天前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
|
6天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
27 3
|
11天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
2天前
|
SQL 存储 Oracle
TDengine 3.3.2.0 发布:新增 UDT 及 Oracle、SQL Server 数据接入
**TDengine 3.3.2.0 发布摘要** - 开源与企业版均强化性能,提升WebSocket、stmt模式写入与查询效率,解决死锁,增强列显示。 - taos-explorer支持geometry和varbinary类型。 - 企业版引入UDT,允许自定义数据转换。 - 新增Oracle和SQL Server数据接入。 - 数据同步优化,支持压缩,提升元数据同步速度,错误信息细化,支持表名修改。 - 扩展跨平台支持,包括麒麟、Euler、Anolis OS等。
9 0
|
15天前
|
SQL 存储 数据库
SQL Server性能优化策略与实践
在使用Microsoft SQL Server(简称MSSQL)进行数据库管理时,性能优化是确保系统高效运行、提升用户体验的关键环节
|
16天前
|
数据库 数据安全/隐私保护
Failed to load resource: the server responded with a status of 404 ()出错的原因是,因为自己调试的时候,设置了与宝塔不一样的数据库
Failed to load resource: the server responded with a status of 404 ()出错的原因是,因为自己调试的时候,设置了与宝塔不一样的数据库
|
SQL 存储 监控
FAQ系列 | 监控平均SQL响应时长
FAQ系列 | 监控平均SQL响应时长
|
1月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
1月前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
253 1