关于SQL Server 定时执行作业的 一点研究(1)
首先SqlServer的作业(job)是定期执行某个语句或者存储过程的任务,类似于windows里面的执行计划。
作业是在SqlServer代理里面,如下面的截图:
主要研究数据库对象：USE [msdb]
主要表及视图：
结构
SELECT * FROM [msdb].[sys].[servers] --视图 SELECT * FROM [msdb].[dbo].[sysjobs_view] --视图 所有作业/Job 显示在SQL Server代理作业下 SELECT * FROM [msdb].[dbo].[sysjobs] --表 作业主要设置信息 SELECT * FROM [msdb].[dbo].[sysjobsteps] --表 作业步骤 SELECT * FROM [msdb].[dbo].[sysschedules] --表 计划执行 设置信息 （主要记录各个作业多长时间执行一次等信息） SELECT * FROM [msdb].[dbo].[sysjobschedules] --表 作业设置计划执行时间信息关系表 SELECT * FROM [msdb].[dbo].[sysjobhistory] --表 作业执行时记录的历史信息 （message 可看具体结果）
说明
SELECT * FROM msdb.dbo.sysjobs --存储将由 SQL Server 代理执行的各个预定作业的信息 SELECT * FROM msdb.dbo.sysjobschedules --包含将由 SQL Server 代理执行的作业的计划信息 SELECT * FROM msdb.dbo.sysjobactivity; --记录当前 SQL Server 代理作业活动和状态 SELECT * FROM msdb.dbo.sysjobservers --存储特定作业与一个或多个目标服务器的关联或关系 SELECT * FROM msdb.dbo.sysjobsteps; --包含 SQL Server 代理要执行的作业中的各个步骤的信息 SELECT * FROM msdb.dbo.sysjobstepslogs; --包含所有 SQL Server 代理作业步骤的作业步骤日志 SELECT * FROM msdb.dbo.sysjobs_view; -- SELECT * FROM msdb.dbo.sysjobhistory --包含有关 SQL Server 代理执行预定作业的信息 SELECT * FROM msdb.dbo.syscategories --包含由 SQL Server Management Studio 用来组织作业、警报和操作员的类别
系统值
-- SQL 邮件相关查询 select * from msdb.dbo.sysmail_allitems select * from msdb.dbo.sysmail_faileditems --失败状态的消息 select * from msdb.dbo.sysmail_unsentitems --看未发送的消息 select * from msdb.dbo.sysmail_sentitems --查看已发送的消息 select * from msdb.dbo.sysmail_event_log --记录日记 SELECT * FROM msdb.dbo.sysmail_event_log WHERE event_type='error' --发送邮件 失败的记录
查询作业基本信息和作业执行情况
------------查询作业基本信息和作业执行情况-- SELECT [jop].[job_id] AS '作业唯一标识符' , [jop].[name] AS '作业名称' , [dp].[name] AS '作业创建者' , [cat].[name] AS '作业类别' , [jop].[description] AS '作业描述' , CASE [jop].[enabled] WHEN 1 THEN '是' WHEN 0 THEN '否' END AS '是否启用' , [jop].[date_created] AS '作业创建日期' , [jop].[date_modified] AS '作业最后修改日期' , [sv].[name] AS '作业运行服务器名称' , [step].[step_id] AS '作业起始步骤' , [step].[step_name] AS '步骤名称' , CASE WHEN [sch].[schedule_uid] IS NULL THEN '否' ELSE '是' END AS '是否分布式作业' , [sch].[schedule_uid] AS '作业计划的唯一标识符' , [sch].[name] AS '作业计划的用户定义名称' , CASE [jop].[delete_level] WHEN 0 THEN '不删除' WHEN 1 THEN '成功后删除' WHEN 2 THEN '失败后删除' WHEN 3 THEN '完成后删除' END AS '作业完成删除选项' FROM [msdb].[dbo].[sysjobs] AS [jop] LEFT JOIN [msdb].[sys].[servers] AS [sv] ON [jop].[originating_server_id] = [sv].[server_id] LEFT JOIN [msdb].[dbo].[syscategories] AS [cat] ON [jop].[category_id] = [cat].[category_id] LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [step] ON [jop].[job_id] = [step].[job_id]AND [jop].[start_step_id] = [step].[step_id] LEFT JOIN [msdb].[sys].[database_principals] AS [dp] ON [jop].[owner_sid] = [dp].[sid] LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [jsch] ON [jop].[job_id] = [jsch].[job_id] LEFT JOIN [msdb].[dbo].[sysschedules] AS [sch] ON [jsch].[schedule_id] = [sch].[schedule_id] ORDER BY [jop].[name];
查看每个作业步骤基本信息
----查看每个作业步骤基本信息--- SELECT [jobstep].[last_run_duration] , [job].[job_id] AS '作业唯一标识符' , [job].[name] AS '作业名称' , [jobstep].[step_uid] AS '作业步骤唯一标识符' , [jobstep].[step_id] AS '步骤编号' , [jobstep].[step_name] AS '步骤名称' , CASE [jobstep].[last_run_outcome] WHEN 0 THEN '失败' WHEN 1 THEN '成功' WHEN 2 THEN '重试' WHEN 3 THEN '取消' WHEN 5 THEN '未知' END AS '上次运行状态' , STUFF(STUFF(RIGHT('000000' + CAST([jobstep].[last_run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)] , [jobstep].[last_run_retries] AS '上次运行重复执行次数' , CASE [jobstep].[last_run_date] WHEN 0 THEN NULL ELSE CAST(CAST([jobstep].[last_run_date] AS CHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST([jobstep].[last_run_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) END AS '上次运行时间' FROM [msdb].[dbo].[sysjobsteps] AS [jobstep] INNER JOIN [msdb].[dbo].[sysjobs] AS [job] ON [jobstep].[job_id] = [job].[job_id] WHERE [jobstep].[database_name] = '数据库的名称' AND UPPER([jobstep].[command]) LIKE UPPER('%执行的命令名称或SQL%') ORDER BY [job].[name] , [jobstep].[step_id];
作业最后执行情况
-----作业最后执行情况---- SELECT [job].[job_id] AS '作业唯一标识符' , [job].[name] AS '作业名称' , [jobstep].[step_uid] AS '步骤唯一标识符' , [jobstep].[step_id] AS '步骤编号' , [jobstep].[step_name] AS '步骤名称' , CASE [jobstep].[subsystem] WHEN 'ActiveScripting' THEN 'ActiveX Script' WHEN 'CmdExec' THEN 'Operating system (CmdExec)' WHEN 'PowerShell' THEN 'PowerShell' WHEN 'Distribution' THEN 'Replication Distributor' WHEN 'Merge' THEN 'Replication Merge' WHEN 'QueueReader' THEN 'Replication Queue Reader' WHEN 'Snapshot' THEN 'Replication Snapshot' WHEN 'LogReader' THEN 'Replication Transaction-Log Reader' WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command' WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query' WHEN 'SSIS' THEN 'SQL Server Integration Services Package' WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)' ELSE [jobstep].[subsystem] END AS '作业步骤类型' , CASE WHEN [px].[name] IS NULL THEN 'SQL SERVER代理服务账户' ELSE [px].[name] END AS '步骤运行账户' , [jobstep].[database_name] AS '执行数据库名' , [jobstep].[command] AS '执行命令' , CASE [jobstep].[on_success_action] WHEN 1 THEN '退出报表成功的作业' WHEN 2 THEN '退出报告失败的作业' WHEN 3 THEN '转到下一步' WHEN 4 THEN '转到步骤: ' + QUOTENAME(CAST([jobstep].[on_success_step_id] AS VARCHAR(3))) + ' ' + [sOSSTP].[step_name] END AS '执行成功后操作' , [jobstep].[retry_attempts] AS '失败时的重试次数' , [jobstep].[retry_interval] AS '重试间的等待时间(分钟)' , CASE [jobstep].[on_fail_action] WHEN 1 THEN '退出报告成功的作业' WHEN 2 THEN '退出报告失败的作业' WHEN 3 THEN '转到下一步' WHEN 4 THEN '转到步骤: ' + QUOTENAME(CAST([jobstep].[on_fail_step_id] AS VARCHAR(3))) + ' ' + [sOFSTP].[step_name] END AS '执行失败后操作' FROM [msdb].[dbo].[sysjobsteps] AS [jobstep] INNER JOIN [msdb].[dbo].[sysjobs] AS [job] ON [jobstep].[job_id] = [job].[job_id] LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP] ON [jobstep].[job_id] = [sOSSTP].[job_id] AND [jobstep].[on_success_step_id] = [sOSSTP].[step_id] LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP] ON [jobstep].[job_id] = [sOFSTP].[job_id] AND [jobstep].[on_fail_step_id] = [sOFSTP].[step_id] LEFT JOIN [msdb].[dbo].[sysproxies] AS [px] --代理账户信息 ON [jobstep].[proxy_id] = [px].[proxy_id] WHERE [jobstep].[database_name] = '数据库名称' AND UPPER([jobstep].[command]) LIKE UPPER('%执行的命令名称或SQL%') ORDER BY [job].[name] , [jobstep].[step_id];
最进见到一篇文章很不错 在此推荐一下 链接 https://www.cnblogs.com/kerrycode/p/3279559.html
