在讲解SQLServer Agent Jobs之前,先要讲解msdb。
Msdb是SQLServer的系统数据库之一,用于存储SQLServer的配置、元数据等信息。包括:
l SQLServer Agent Jobs,Job Steps,Job schedules,Alerts,Operators,等等。
l Service Broker,Log Shipping,Backups/restore信息,维护计划、数据库邮件、基于策略管理信息等等。
l SSIS包。
在这部分,主要集中在msdb的以下部分:
l Job setup/configuration Information
l Job Execution Information
l Job Step(s) Setup/Configuration Information
l Job Step(s) Execution Information
l Schedule Information
————————————————————————————————————————————————————————————————————————————
SQLServer 允许在Replication,SSIS,存储过程,批处理上创建和执行各种自动化任务。允许使用GUI 或者T-SQL脚本创建。这些信息存储在msdb中。SQL Server Agent Job Setup andConfiguration Information:
可以在SSMS中执行以下脚本查找作业信息:
- SELECT [sJOB].[job_id] AS [作业ID] ,
- [sJOB].[name] AS [作业名称] ,
- [sDBP].[name] AS [作业创建者] ,
- [sCAT].[name] AS [作业种类] ,
- [sJOB].[description] AS [作业描述] ,
- CASE [sJOB].[enabled]
- WHEN 1 THEN '已启用'
- WHEN 0 THEN '未启用'
- END AS [是否启用] ,--
- [sJOB].[date_created] AS [作业创建日期] ,
- [sJOB].[date_modified] AS [作业最后修改日期] ,
- [sSVR].[name] AS [作业运行服务器] ,
- [sJSTP].[step_id] AS [作业起始步骤] ,
- [sJSTP].[step_name] AS [步骤名称] ,
- CASE WHEN [sSCH].[schedule_uid] IS NULL THEN '否'
- ELSE '是'
- END AS [是否分布式作业] ,
- [sSCH].[schedule_uid] AS [分布式作业ID] ,
- [sSCH].[name] AS [用户定义名称] ,
- CASE [sJOB].[delete_level]
- WHEN 0 THEN '不删除'
- WHEN 1 THEN '成功后删除'
- WHEN 2 THEN '失败后删除'
- WHEN 3 THEN '完成时删除'
- END AS [完成时删除作业级别]
- FROM [msdb].[dbo].[sysjobs] AS [sJOB]
- LEFT JOIN [msdb].[sys].[servers] AS [sSVR] ON [sJOB].[originating_server_id] = [sSVR].[server_id]
- LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]
- LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP] ON [sJOB].[job_id] = [sJSTP].[job_id]
- AND [sJOB].[start_step_id] = [sJSTP].[step_id]
- LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]
- LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
- LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
- ORDER BY [作业名称]
SQL Server Agent Job Execution Information:
SQLServer同时存放作业执行信息在msdb中。可以执行以下脚本查询作业执行情况:
- SELECT [sJOB].[job_id] AS [作业ID] ,
- [sJOB].[name] AS [作业名] ,
- CASE WHEN [sJOBH].[run_date] IS NULL
- OR [sJOBH].[run_time] IS NULL THEN NULL
- ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + ' '
- + STUFF(STUFF(RIGHT('000000'
- + CAST([sJOBH].[run_time] AS VARCHAR(6)),
- 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
- END AS [最近执行时间] ,
- CASE [sJOBH].[run_status]
- WHEN 0 THEN '失败'
- WHEN 1 THEN '成功'
- WHEN 2 THEN '重试'
- WHEN 3 THEN '取消'
- WHEN 4 THEN '正在运行' -- In Progress
- END AS [最近执行状态] ,
- STUFF(STUFF(RIGHT('000000'
- + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3,
- 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)] ,
- [sJOBH].[message] AS [最近运行状态信息] ,
- CASE [sJOBSCH].[NextRunDate]
- WHEN 0 THEN NULL
- ELSE CAST(CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) + ' '
- + STUFF(STUFF(RIGHT('000000'
- + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),
- 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
- END AS [下次运行时间]
- FROM [msdb].[dbo].[sysjobs] AS [sJOB]
- LEFT JOIN ( SELECT [job_id] ,
- MIN([next_run_date]) AS [NextRunDate] ,
- MIN([next_run_time]) AS [NextRunTime]
- FROM [msdb].[dbo].[sysjobschedules]
- GROUP BY [job_id]
- ) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
- LEFT JOIN ( SELECT [job_id] ,
- [run_date] ,
- [run_time] ,
- [run_status] ,
- [run_duration] ,
- [message] ,
- ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber
- FROM [msdb].[dbo].[sysjobhistory]
- WHERE [step_id] = 0
- ) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id]
- AND [sJOBH].[RowNumber] = 1
- ORDER BY [作业名]
SQL Server Anget Job Steps Setup andconfiguration Information:
在作业系统中,一个作业是有层级的,可以包含一个或多个步骤。
运行以下脚本查看作业步骤信息:
- SELECT [sJOB].[job_id] AS [作业ID] ,
- [sJOB].[name] AS [作业名] ,
- [sJSTP].[step_uid] AS [步骤ID] ,
- [sJSTP].[step_id] AS [步骤序号] ,
- [sJSTP].[step_name] AS [步骤名] ,
- CASE [sJSTP].[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 sJSTP.subsystem
- END AS [作业子系统类型] ,
- [sPROX].[name] AS [作业运行账号] ,
- [sJSTP].[database_name] AS [执行数据库名] ,
- [sJSTP].[command] AS [执行命令] ,
- CASE [sJSTP].[on_success_action]
- WHEN 1 THEN 'Quit the job reporting success'
- WHEN 2 THEN 'Quit the job reporting failure'
- WHEN 3 THEN 'Go to the next step'
- WHEN 4
- THEN 'Go to Step: '
- + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
- + ' ' + [sOSSTP].[step_name]
- END AS [执行成功后反应] ,
- [sJSTP].[retry_attempts] AS [失败时的重试次数] ,
- [sJSTP].[retry_interval] AS [重试间的等待时间 (Minutes)] ,
- CASE [sJSTP].[on_fail_action]
- WHEN 1 THEN 'Quit the job reporting success'
- WHEN 2 THEN 'Quit the job reporting failure'
- WHEN 3 THEN 'Go to the next step'
- WHEN 4
- THEN 'Go to Step: '
- + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
- + ' ' + [sOFSTP].[step_name]
- END AS [执行失败后反映]
- FROM [msdb].[dbo].[sysjobsteps] AS [sJSTP]
- INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id]
- LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP] ON [sJSTP].[job_id] = [sOSSTP].[job_id]
- AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
- LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP] ON [sJSTP].[job_id] = [sOFSTP].[job_id]
- AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
- LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX] ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
- ORDER BY [作业名] ,
- [步骤序号]
SQL Server Anget Job Steps ExecutionInformation:
在msdb中同样存储了步骤的执行计划,执行以下语句检查:
- SELECT [sJOB].[job_id] AS [作业ID] ,
- [sJOB].[name] AS [作业名称] ,
- [sJSTP].[step_uid] AS [步骤ID] ,
- [sJSTP].[step_id] AS [步骤序号] ,
- [sJSTP].[step_name] AS [步骤名称] ,
- CASE [sJSTP].[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([sJSTP].[last_run_duration] AS VARCHAR(6)), 6),
- 3, 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)] ,
- [sJSTP].[last_run_retries] AS [上次重试次数] ,
- CASE [sJSTP].[last_run_date]
- WHEN 0 THEN NULL
- ELSE CAST(CAST([sJSTP].[last_run_date] AS CHAR(8)) + ' '
- + STUFF(STUFF(RIGHT('000000'
- + CAST([sJSTP].[last_run_time] AS VARCHAR(6)),
- 6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
- END AS [上次运行时间]
- FROM [msdb].[dbo].[sysjobsteps] AS [sJSTP]
- INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id]
- ORDER BY [作业名称] ,
- [步骤序号]
SQL Server Agent Job Sechdule Information:
SQLServer允许在特定时间创建各种计划,每个计划能组合成一个或多个SQLServer Agent Jobs。执行以下脚本查询情况:
- SELECT [schedule_uid] AS [作业计划ID] ,
- [name] AS [作业计划名称] ,
- CASE [enabled]
- WHEN 1 THEN '已启用'
- WHEN 0 THEN '未启用'
- END AS [是否启用] ,
- CASE WHEN [freq_type] = 64
- THEN 'Start automatically when SQL Server Agent starts'
- WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
- WHEN [freq_type] IN ( 4, 8, 16, 32 ) THEN 'Recurring'
- WHEN [freq_type] = 1 THEN 'One Time'
- END [作业计划类型] ,
- CASE [freq_type]
- WHEN 1 THEN 'One Time'
- WHEN 4 THEN 'Daily'
- WHEN 8 THEN 'Weekly'
- WHEN 16 THEN 'Monthly'
- WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
- WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
- WHEN 128 THEN 'Start whenever the CPUs become idle'
- END [作业运行频率] ,
- CASE [freq_type]
- WHEN 4
- THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3))
- + ' day(s)'
- WHEN 8
- THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
- + ' week(s) on '
- + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday'
- ELSE ''
- END + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday'
- ELSE ''
- END
- + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday'
- ELSE ''
- END + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday'
- ELSE ''
- END
- + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday'
- ELSE ''
- END + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday'
- ELSE ''
- END
- + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday'
- ELSE ''
- END
- WHEN 16
- THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3))
- + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
- + ' month(s)'
- WHEN 32
- THEN 'Occurs on ' + CASE [freq_relative_interval]
- WHEN 1 THEN 'First'
- WHEN 2 THEN 'Second'
- WHEN 4 THEN 'Third'
- WHEN 8 THEN 'Fourth'
- WHEN 16 THEN 'Last'
- END + ' ' + CASE [freq_interval]
- WHEN 1 THEN 'Sunday'
- WHEN 2 THEN 'Monday'
- WHEN 3 THEN 'Tuesday'
- WHEN 4 THEN 'Wednesday'
- WHEN 5 THEN 'Thursday'
- WHEN 6 THEN 'Friday'
- WHEN 7 THEN 'Saturday'
- WHEN 8 THEN 'Day'
- WHEN 9 THEN 'Weekday'
- WHEN 10 THEN 'Weekend day'
- END + ' of every '
- + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
- END AS [循环间隔] ,
- CASE [freq_subday_type]
- WHEN 1
- THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000'
- + CAST([active_start_time] AS VARCHAR(6)),
- 6), 3, 0, ':'), 6, 0, ':')
- WHEN 2
- THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3))
- + ' Second(s) between ' + STUFF(STUFF(RIGHT('000000'
- + CAST([active_start_time] AS VARCHAR(6)),
- 6), 3, 0, ':'), 6,
- 0, ':') + ' & '
- + STUFF(STUFF(RIGHT('000000'
- + CAST([active_end_time] AS VARCHAR(6)), 6),
- 3, 0, ':'), 6, 0, ':')
- WHEN 4
- THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3))
- + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000'
- + CAST([active_start_time] AS VARCHAR(6)),
- 6), 3, 0, ':'), 6,
- 0, ':') + ' & '
- + STUFF(STUFF(RIGHT('000000'
- + CAST([active_end_time] AS VARCHAR(6)), 6),
- 3, 0, ':'), 6, 0, ':')
- WHEN 8
- THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3))
- + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000'
- + CAST([active_start_time] AS VARCHAR(6)),
- 6), 3, 0, ':'), 6, 0,
- ':') + ' & '
- + STUFF(STUFF(RIGHT('000000'
- + CAST([active_end_time] AS VARCHAR(6)), 6),
- 3, 0, ':'), 6, 0, ':')
- END [计划运行频率] ,
- STUFF(STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0,
- '-') AS [作业启用开始时间] ,
- STUFF(STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0,
- '-') AS [作业启用结束时间] ,
- [date_created] AS [作业创建日期] ,
- [date_modified] AS [作业上次修改日期]
- FROM [msdb].[dbo].[sysschedules]
- ORDER BY [作业计划名称]
相关推荐
在上一篇文章中已经分析了SQL SERVER中关于邮件的基础配置,本篇将利用此功能对多台Server的跑批Job进行监控。 本篇实现 1、每天检查服务器中的SQL Server跑批Job的运行状态,如果跑批失败,则发邮件告诉...
当使用CPU计数器测量CPU活动时,记住下面是SQL Server中耗用CPU资源最多的进程: • 上下文切换:当SQL Server在多个CPU之间切换线程时就会发生上下文切换,过多的上下文切换会吃掉CPU资源。有些情况下,打开...
SQL Server 2005 ETL专家系列之六:SQL Server 2005 Integration Service的运行管理 讲师信息:陈亮 2007年03月27日 14:00-15:30 Level: 200 包已经设计好了,如何用作业来进行调度?如何在设计环境外运行这些包...
C# + SQL Server 本系统包括两类用户:学生、管理员。管理员可以通过系统来添加管理员信息、修改管理员信息、添加学生信息、修改学生信息;开设课程、查询课程、录入成绩、统计成绩 管理系统是一种通过计算机技术...
模拟温度监控系统源码(Android+Java+SQL Server 移动终端(Android)通过Socket与服务器连接,通过用户登录信息与数据库的ID相匹配,通过Android定时器按一定周期生产温度数据,同时通过线程来周期绘制温度趋势的UI...
铁路货运站运输安全监测管理系统网站C#+ASP.NET+SQLserver 目的及任务 1.以货运中心铁路货场为例,以货场内的各项设备、人员为监测对象,结合货场基本作业过程开发安全监测系统,起到预警作用。 2.阐述铁路货运站...
我创建了一个每10分钟运行的作业,保存状态的历史记录数据到一个表,数据保留14天。 这个表在订阅者服务器的DBA数据库创建,代码如下: CREATE TABLE dbo.Replication_Qu_History( Subscriber_db varchar...
目的:利用Sqlserver代理的邮件会话建立操作员监控作业完成情况 测试环境: 操作系统:windows 2003/windows xp OutLook: OutLook 2003 测试的电子邮件: yyy@xxx.com 第一步:建配置文件开始->控制面版->邮件>双击...
C# + SQL Server 本系统包括两类用户:学生、管理员。管理员可以通过系统来添加管理员信息、修改管理员信息、添加学生信息、修改学生信息;开设课程、查询课程、录入成绩、统计成绩 管理系统是一种通过计算机技术...
随着市场经济的高速发展,制造业、商业企业逐步接受着第三方物流服务...系统支持多形式客户的数据共享、提高作业效率、监控整个流程、提供完整的货品、进行订单跟踪,并且特别关注对各环节业务的监控及例外情况的监控。
管理系统是一种通过计算机技术实现的用于组织、监控和控制各种活动的软件系统。这些系统通常被设计用来提高效率、减少错误、加强安全性,同时提供数据和信息支持。以下是一些常见类型的管理系统: 学校管理系统: ...
课程设计JavaWeb大作业web电脑考试系统项目源码和数据库,内含详细使用说明,项目文档,初学者的福音啊(捂脸)高手可以二次开发 项目特色 1. 精美炫酷的登录页面 2. 设计合理,左侧式导航栏,顶部二级菜单 3. 32位 ...
C# + SQL Server 本系统包括两类用户:学生、管理员。管理员可以通过系统来添加管理员信息、修改管理员信息、添加学生信息、修改学生信息;开设课程、查询课程、录入成绩、统计成绩 管理系统是一种通过计算机技术...
管理系统是一种通过计算机技术实现的用于组织、监控和控制各种活动的软件系统。这些系统通常被设计用来提高效率、减少错误、加强安全性,同时提供数据和信息支持。以下是一些常见类型的管理系统: 学校管理系统: ...
C# + SQL Server 本系统包括两类用户:学生、管理员。管理员可以通过系统来添加管理员信息、修改管理员信息、添加学生信息、修改学生信息;开设课程、查询课程、录入成绩、统计成绩 管理系统是一种通过计算机技术...
爱数备份软件将完整备份与日志备份自动...利用集中式管理工具的帮助,系统管理员可对全网的备份策略进行统一管理,备份服务器可以监控所有机器的备份作业,也可以修改备份策略,具有自动删除老数据功能多种加密算法。
C# + SQL Server 本系统包括两类用户:学生、管理员。管理员可以通过系统来添加管理员信息、修改管理员信息、添加学生信息、修改学生信息;开设课程、查询课程、录入成绩、统计成绩 管理系统是一种通过计算机技术...
C# + SQL Server 本系统包括两类用户:学生、管理员。管理员可以通过系统来添加管理员信息、修改管理员信息、添加学生信息、修改学生信息;开设课程、查询课程、录入成绩、统计成绩 管理系统是一种通过计算机技术...
4PostgreSQL 触发器99PostgreSQL 触发函数100PostgreSQL 类型102PostgreSQL...SQL Server 对象115SQL Server 备份或还原(只限于完整版本)120SQL Server 备份120SQL Server 还原122SQL Server 模式123SQL Server 表124...
C# + SQL Server 本系统包括两类用户:学生、管理员。管理员可以通过系统来添加管理员信息、修改管理员信息、添加学生信息、修改学生信息;开设课程、查询课程、录入成绩、统计成绩 管理系统是一种通过计算机技术...