有很多地方可以设置定时任务,比如: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) 部署作业:定时检查其他所有作业/步骤状态,发邮件告警;
作业运行时长可以在这一并检查,有时一些作业运行了很多天没结束还没人知道,也可以考虑放在性能监控里,和其他数据库请求一起监控。但是对于时长,通常需要有个性能基线,如果没有的话直接和历史最大值相比也是不错的选择。

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

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

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

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

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

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

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

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


热AI工具

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

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

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

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

热门文章

热工具

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

EditPlus 中文破解版
体积小,语法高亮,不支持代码提示功能

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

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

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)