SQL SERVER 作业浅析
创建作业、删除作业、查看作业历史记录....等所有操作都可以通过SSMS管理工具GUI界面操作,有时候也确实挺方便的。但是当一个实例有多个作业或 多个数据库实例时,通过图形化的界面去管理、维护作业也是个头痛的问题,对于SQL脚本与GUI界面管理维护作业熟优熟劣这个问题,只能说要看场合。下面 主要介绍通过SQL脚本来管理、维护作业。
作业分类
创建作业时,往往需要指定作业类别,如果不指定新建作业类别,就会默认为“[未分类(本地)]”,如下截图所示:
当然,你可以查看、添加、删除、修改作业分类。请看下面操作。
1:查看作业分类
IF @name IS NOT NULL AND NOT EXISTS(SELECT * FROM msdb.dbo.syscategories WHERE name = @name AND category_class = @category_class) BEGIN DECLARE @category_class_string NVARCHAR(25) SET @category_class_string = CAST(@category_class AS nvarchar(25)) RAISERROR(14526, -1, -1, @name, @category_class_string) RETURN(1) SELECT @where_clause = NWHERE (category_class = + CONVERT(NVARCHAR, @category_class) + N) IF (@name IS NOT NULL) SELECT @where_clause = @where_clause + NAND (name = N + QUOTENAME(@name, ) + N) IF (@type IS NOT NULL) SELECT @where_clause = @where_clause + NAND (category_type = + CONVERT(NVARCHAR, @category_type) + N) SELECT @cmd = NSELECT category_id, IF (@suffix = 1) BEGIN SELECT @cmd = @cmd + Ncategory_type = SELECT @cmd = @cmd + NCASE category_type SELECT @cmd = @cmd + NWHEN 0 THEN NONE SELECT @cmd = @cmd + NWHEN 1 THEN LOCAL SELECT @cmd = @cmd + NWHEN 2 THEN MULTI-SERVER SELECT @cmd = @cmd + NWHEN 3 THEN NONE SELECT @cmd = @cmd + NELSE FORMATMESSAGE(14205) SELECT @cmd = @cmd + NEND, BEGIN SELECT @cmd = @cmd + Ncategory_type, SELECT @cmd = @cmd + Nname SELECT @cmd = @cmd + NFROM msdb.dbo.syscategories EXECUTE (@cmd + @where_clause + NORDER BY category_type, name) RETURN(@@error) sp_help_category
2:添加作业分类
如下所示,添加一个叫"DBA_MONITORING"的作业分类
----------- -------------- ------------- ------------- 102 1 1 DBA_MONITORING
INSERT INTO msdb.dbo.syscategories (category_class, category_type, name) VALUES (@category_class, @category_type, @name) RETURN(@@error) sp_add_category
3:删除作业分类
如下所示,删除一个叫"DBA_MONITORING" 的作业分类
IF @name IS NOT NULL AND NOT EXISTS(SELECT * FROM msdb.dbo.syscategories WHERE name = @name AND category_class = @category_class) BEGIN RAISERROR(14526, -1, -1, @name, @category_class) RETURN(1) SELECT @category_id = category_id FROM msdb.dbo.syscategories WHERE (category_class = @category_class) AND (name = @new_name) IF (@category_id IS NOT NULL) BEGIN RAISERROR(14261, -1, -1, @new_name, @new_name) RETURN(1) IF (@category_id 100) BEGIN RAISERROR(14276, -1, -1, @name, @class) RETURN(1) UPDATE msdb.dbo.syscategories SET name = @new_name WHERE (category_class = @category_class) AND (name = @name) RETURN(@@error) sp_update_category
分析上面四个存储过程可以看出,实质上新增、修改、删除、查看作业类别无非就是对表 msdb.dbo.syscategories进行操作,只是通过存储过程封装了而已,增加了验证等操作,确保数据完整性。
新建作业
创建作业的步骤一般如下所示:
执行 sp_add_job 来创建作业。执行 sp_add_jobstep 来创建一个或多个作业步骤。
本地作业是由本地 SQL Server 代理进行缓存的。因此,任何修改都会隐式强制 SQL Server 代理重新缓存该作业。由于直到调用 sp_add_jobserver 时,SQL Server 代理才缓存作业,因此最后调用 sp_add_jobserver 将更为有效。
下面看用脚本新建一个作业用来每天执行exec sp_cycle_errorlog ,实现错误日志循环, 从下面的脚本量来看,用脚本新建一个作业确实工作量很大,而且容易出错,GUI图形界面创建作业要方便得多,但是如果迁移数据库时,用脚本来新建作业是相 当方便的。比GUI图形界面新建一个作业快捷方便多了。
/****** Object: Job [JOB_CYCLE_ERRORLOG] Script Date: 08/23/2013 15:25:09 ******/ IFEXISTS(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = NJOB_CYCLE_ERRORLOG)
EXEC msdb.dbo.sp_delete_job@job_id=Na5dff08b-95f8-498e-a6c9-59241fe197b4, @delete_unused_schedule=1 USE [msdb] /****** Object: Job [JOB_CYCLE_ERRORLOG] Script Date: 08/23/2013 15:25:09 ******/ BEGIN TRANSACTION
SELECT @ReturnCode = 0 /****** Object: JobCategory [DBA_MATIANCE] Script Date: 08/23/2013 15:25:09 ******/ IF NOT EXISTS(SELECT name FROM msdb.dbo.syscategories WHERE name=NDBA_MATIANCE AND category_ JOB, @type=NLOCAL, @name=NDBA_MATIANCE
/****** Object: Step [Step 1: recycle the errorlog] Script Date: 08/23/2013 15:25:09 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep@job_id=@jobId, @step_name=NStep 1: recycle the errorlog, @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=NTSQL, @command=Nexec msdb.dbo.sp_cycle_errorlog, @database_name=Nmsdb, @flags=0 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N(local)
作业系统表
与作业有关的系统表、视图大致有下面9个,下面就不费口舌详细解说每一个系统表的作用了,MSDN文档上有详细的解说,有兴趣的翻看一下即可。
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 用来组织作业、警报和操作员的类别
运行作业
启动作业
1:通过SSMS工具启动作业[参见MSDN]
2:通过SQL命令启动作业
启动作业一般通过sp_start_job来实现,具体语法与操作见下面。
语法: sp_start_job { [@job_name =] job_name | [@job_id =] job_id } [ , [@error_flag =] error_flag] [ , [@server_name =] server_name] [ , [@step_name =] step_name] [ , [@output_flag =] output_flag] exec msdb.dbo.sp_start_job @job_name=JOB_CYCLE_ERRORLOG
停止作业
1:通过SSMS工具停作业[参见MSDN]
2:通过SQL命令停止作业
语法: sp_stop_job [@job_name =] job_name | [@job_id =] job_id | [@originating_server =] master_server | [@server_name =] target_server exec msdb.dbo.sp_stop_job @job_name=JOB_CYCLE_ERRORLOG
sp_update_job [ @job_id =] job_id | [@job_name =] job_name
[, [@new_name =] new_name ]
[, [@enabled =] enabled ]
[, [@description =] description ]
[, [@start_step_id =] step_id ]
[, [@category_name =] category ]
[, [@owner_login_name =] login ]
[, [@notify_level_eventlog =] eventlog_level ]
[, [@notify_level_email =] email_level ]
[, [@notify_level_netsend =] netsend_level ]
[, [@notify_level_page =] page_level ]
[, [@notify_email_operator_name =] email_name ]
[, [@notify_netsend_operator_name =] netsend_operator ]
[, [@notify_page_operator_name =] page_operator ]
[, [@delete_level =] delete_level ]
[, [@automatic_post =] automatic_post ]
EXEC msdb.dbo.sp_update_job
@job_name = NJOB_CYCLE_ERRORLOG,
@enabled = 0 ; --0 禁用作业、 1启用作业
GO
删除作业
1:通过SSMS工具删除作业[参见MSDN]
2:通过SQL命令删除作业
语法: sp_delete_job { [ @job_id = ] job_id | [ @job_name = ] job_name } , [ , [ @originating_server = ] server ] [ , [ @delete_history = ] delete_history ] [ , [ @delete_unused_schedule = ] delete_unused_schedule ] EXEC msdb.dbo.sp_delete_job @job_name = JOB_CYCLE_ERRORLOG;
迁移作业
使用 Transact-SQL 编写作业脚本从快捷菜单中,选择“编写作业脚本为”,再选择“CREATE 到”或“DROP 到”,并单击下列内容之一:
新查询编辑器窗口,将打开一个新的查询编辑器窗口,并为其编写 Transact-SQL 脚本。
文件,将 Transact-SQL 脚本保存到文件。
3:查看禁用/启用的作业
SELECT * FROM msdb.dbo.sysjobs WHERE enabled=0 --0:禁用 1:为启用
4:查看出错的作业记录
4.1:查询那些作业在今天出错(如果要查询历史出错作业,去掉查询时间条件即可)
WHERE run_status = 0 AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT) )
WHERE run_status = 0 AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
5:查看作业的执行时间:
5.1:查看当天成功执行的作业的时间(查看的是作业Step信息)
WHERE run_status = 1 AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT) ORDER BY run_duration DESC
WHERE run_status = 1 AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
[ SQL Server ] 计算N个月前,N个月后的方法 #1. 计算N个月前的日期 #2. 计算N个月后的日期 #3. 月末最后一天的情况(计算从日数多的月到日数较少的月) #4. 月末最后一天的情况(计算从日数少的月到日数较多的月)
【SQL Server】数据库开发指南(三)面向数据分析的 T-SQL 编程技巧与实践 T-SQL 指的是 Transact-SQL,是一种针对 Microsoft SQL Server 数据库系统的 SQL 方言。T-SQL 扩展了标准 SQL 语言,提供了更多的功能和特性,包括事务处理、错误处理、游标处理、动态 SQL、存储过程、触发器、用户定义函数等等。
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载
相关文章
- sql server 备份还原_什么是SQL
- 浅析SQL Server中的执行计划缓存(上)
- 深入浅析SQL Server 触发器
- SQL Server 提取数字、提取英文、提取中文的sql语句
- 浅析SQL Server中包含事务的存储过程
- 在SQL Server中查询资料库的TABLE数量与名称的sql语句
- 在SQL Server中使用事务(sqlserver中事务)
- 和 sql server支持两者:Oracle 和 SQL Server的兼容性(兼容oracle)
- SQL Server窗口:充分利用强大功能(窗口 sqlserver)
- 查看SQL Server进程:一种实用方法(查sqlserver进程)
- SQL Server表之间的链接:探索新机遇(sqlserver表链接)
- SQL Server技术在数据库管理中的应用研究(sqlserver小论文)
- 挑战SQL Server大师,赢取荣耀!(sqlserver大师)
- 备份实现SQL Server多级备份,保障数据安全(sqlserver多级)
- sql两大主流数据库之比较—SQL Server和MySQL(sqlserver和my)
- sql探索SQL Server:前六条SQL语句(sqlserver前六条)
- SQL Server提高学习效率:从分数开始(sqlserver 分数)
- SQL Server如何快速写出好用的提示(sqlserver写提示)
- 利用SQL Server实现关联表间数据交互(sqlserver关联表)
- SQL Server的安装包为什么也有绿色版?探究SQL Server绿色版的特点与使用方法。(sqlserver绿色)
- SQL Server导出SQL文件:实现快速数据备份(sqlserver导出sql文件)