SqlServer代理作业
最近一直在学习SqlServer 作业方面的知识,总结一下。
一:作业存在的库。
msdb use msdb
Msdb数据库是代理服务数据库,为其报警、任务调度和记录操作员的操作提供存储空间。
二:查看作业分类
1 2 3 4 5 6 | EXEC msdb.dbo.sp_help_category; SELECT category_id , --作业类别ID category_class , --类别中项目类型:1=作业2=警报 3=操作员 category_type , --类别中类型:=本地、=多服务器、=无 name --分类名称 from msdb.dbo.syscategories |
三:作业常用的几个步骤:
1 2 3 4 5 6 | EXEC msdb.dbo.sp_delete_job EXEC msdb.dbo.sp_add_job EXEC msdb.dbo.sp_add_jobstep EXEC msdb..sp_add_jobschedule EXEC msdb.dbo.sp_add_jobserver EXEC msdb.dbo.sp_start_job |
四:写一个简单的案例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | begin transaction --申明变量 declare @ReturnCode INT --赋值 select @ReturnCode = 0 --如果不包含 if not exists ( select * from msdb.dbo.syscategories where name =N '[Uncategorized (Local)]' and category_class=1) begin --添加作业分类 exec @ReturnCode=msdb.dbo.sp_add_category @class=N 'JOB' ,@type=N 'LOCAL' ,@ name =N '[Uncategorized (Local)]' --上一条语句是否有错 @ReturnCode 是否赋值 QuitWithRollback 回滚事务 if (@@ERROR<>0 or @ReturnCode<>0) GOTO QuitWithRollback end declare @jobId BINARY (16) --名字 第一步 sp_add_job EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N 'Proc_SyncBaseData' , @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N '无描述。' , @category_name=N '[Uncategorized (Local)]' , --登录名称 @jobId有返回值 @owner_login_name=N 'sa' , @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback --第二部 sp_add_jobstep exec @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N 'Proc_SyncBaseData' , --作业名称 @step_id=1, @cmdexec_success_code=0, @on_success_action=3, @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=N 'TSQL' , --步骤的类型 @command=N 'exec Proc_SyncBaseData' , --执行的作业 @database_name=N 'TOPK_DATA' , @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback --处理基础数据 exec @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N '处理基础数据' , @step_id=2, @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=N 'TSQL' , @command=N 'DECLARE @num INT=1<br> --这儿包括了一些业务代码就不写了<br> @database_name=N' TOPK_DATA ', --数据库名称<br> @flags=0<br> --回滚<br> IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback<br> --修改<br> EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1<br><br> IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback<br><br> EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N' 1 ', <br> @enabled=1, <br> @freq_type=4, <br> @freq_interval=1, <br> @freq_subday_type=1, <br> @freq_subday_interval=0, <br> @freq_relative_interval=0, <br> @freq_recurrence_factor=0, <br> @active_start_date=20151231, <br> @active_end_date=99991231, <br> @active_start_time=10000, <br> @active_end_time=235959, <br> @schedule_uid=N' 81392edd-5c9d-4bf0-a306-4ff22a0920cf '<br> IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback<br> EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N' ( local )'<br> IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback<br> COMMIT TRANSACTION <br> GOTO EndSave<br>QuitWithRollback:<br> IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION <br>EndSave:<br><br> |
参考的案例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 | --删除作业 IF EXISTS ( SELECT JOB_ID FROM MSDB.DBO.SYSJOBS_VIEW WHERE NAME =N '作业名称' ) EXECUTE MSDB.DBO.SP_DELETE_JOB @JOB_NAME=N '作业名称' --定义创建作业 DECLARE @jobid uniqueidentifier EXEC msdb.dbo.sp_add_job @job_name = N '作业名称' , @job_id = @jobid OUTPUT --定义作业步骤 DECLARE @sql nvarchar(400),@dbname sysname SELECT @dbname=DB_NAME(), --执行的数据库(当前) @sql=N '作业步骤内容' --一般定义的是使用TSQL处理的作业,这里定义要执行的Transact-SQL语句 EXEC msdb.dbo.sp_add_jobstep @job_id = @jobid, @step_name = N '作业步骤名称' , @subsystem = 'TSQL' , --步骤的类型,一般为TSQL @database_name=@dbname, @command = @sql --创建调度(使用后面专门定义的几种作业调度模板) EXEC msdb..sp_add_jobschedule @job_id = @jobid, @ name = N '调度名称' , @freq_type=4, --每天 @freq_interval=1, --指定每多少天发生一次,这里是1天. @freq_subday_type=0x8, --重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次 @freq_subday_interval=1, --重复周期数,这里每小时执行一次 @active_start_date = NULL , --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD @active_end_date = 99991231, --作业执行的停止日期,默认为99991231,格式为YYYYMMDD @active_start_time = 00000, --作业执行的开始时间,格式为HHMMSS @active_end_time = 235959 --作业执行的停止时间,格式为HHMMSS --添加目标服务器 DECLARE @servername sysname SET @servername= CONVERT (nvarchar(128),SERVERPROPERTY(N 'ServerName' )) --当前SQL实例 EXEC msdb.dbo.sp_add_jobserver @job_id = @jobid, @server_name = @servername ----------------------------------------------------------------------------------------------- --调度模板定义 sp_add_jobschedule --只执行一次的作业调度 EXEC msdb..sp_add_jobschedule @job_id = @jobid, @ name = N '调度名称' , @freq_type=1, --仅执行一次 @active_start_date = NULL , --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD @active_start_time = 00000 --作业执行的开始时间,格式为HHMMSS --日调度 EXEC msdb..sp_add_jobschedule @job_id = @jobid, @ name = N '调度名称' , @freq_type=4, --每天 @freq_interval=1, --指定每多少天发生一次,这里是1天. @freq_subday_type=0x8, --重复方式,(0x1=指定的时间;0x4=多少分钟;0x8=多少小时)执行一次 @freq_subday_interval=1, --重复周期数,这里每小时执行一次 @active_start_date = NULL , --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD @active_end_date = 99991231, --作业执行的停止日期,默认为99991231,格式为YYYYMMDD @active_start_time = 00000, --作业执行的开始时间,格式为HHMMSS @active_end_time = 235959 --作业执行的停止时间,格式为HHMMSS --周调度 EXEC msdb.dbo.sp_add_jobschedule @job_id = @jobid, @ name = N '调度名称' , @freq_type = 8, --每周 @freq_recurrence_factor = 1, --每多少周执行一次,这里是每周 @freq_interval = 62, --在星期几执行,由POWER(2,N)表示,N的值为0~6,代表星期日~星期六,如果指定两个,则将值相加,例如,值为65表示在星期天和星期日执行(POWER(2,0)+POWER(2,6)) @freq_subday_type = 0x8, --重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次 @freq_subday_interval = 1, --重复周期数,这里每小时执行一次 @active_start_date = NULL , --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD @active_end_date = 99991231, --作业执行的停止日期,默认为99991231,格式为YYYYMMDD @active_start_time = 00000, --作业执行的开始时间,格式为HHMMSS @active_end_time = 235959 --作业执行的停止时间,格式为HHMMSS --月调度(每X个月的每月几号) EXEC msdb.dbo.sp_add_jobschedule @job_id = @jobid, @ name = N '调度名称' , @freq_type = 16, --每月 @freq_recurrence_factor = 2, --每多少月执行一次,这里是每2个月 @freq_interval = 2, --在执行月的第几天执行,这里是第2天 @freq_subday_type = 0x8, --重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次 @freq_subday_interval = 1, --重复周期数,这里每小时执行一次 @active_start_date = NULL , --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD @active_end_date = 99991231, --作业执行的停止日期,默认为99991231,格式为YYYYMMDD @active_start_time = 00000, --作业执行的开始时间,格式为HHMMSS @active_end_time = 235959 --作业执行的停止时间,格式为HHMMSS --月调度(每X个月的相对时间) EXEC msdb.dbo.sp_add_jobschedule @job_id = @jobid, @ name = N '调度名称' , @freq_type = 32, --每月 @freq_recurrence_factor = 2, --每多少月执行一次,这里是每2个月 @freq_interval = 9, --在当月的那个时间执行,1~7=星期日至星期六,8=日 ,9=工作日,10=周末 @freq_relative_interval = 1, --在第几个相对时间执行,允许的值为1,2,4,8代表第1~4个相对时间,16表示最后一个相对时间 @freq_subday_type = 0x8, --重复方式,0x1=在指定的时间,0x4=多少分钟,0x8=多少小时执行一次 @freq_subday_interval = 1, --重复周期数,这里每小时执行一次 @active_start_date = NULL , --作业执行的开始日期,为NULL时表示当前日期,格式为YYYYMMDD @active_end_date = 99991231, --作业执行的停止日期,默认为99991231,格式为YYYYMMDD @active_start_time = 00000, --作业执行的开始时间,格式为HHMMSS @active_end_time = 235959 --作业执行的停止时间,格式为HHMMSS --在特定时候执行的作业调度 EXEC msdb.dbo.sp_add_jobschedule @job_id = @jobid, @ name = N '调度名称' , @freq_type = 64 --64=在SQLServerAgent 服务启动时运行,128=计算机空闲时运行 ----------------------------------------------------------------------------------------------- [sql] view plain copy 在CODE上查看代码片派生到我的代码片 IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N '[DBO].SP_AUTO_CREATEJOB' ) AND OBJECTPROPERTY(ID, N 'ISPROCEDURE' ) = 1) DROP PROCEDURE [DBO].SP_AUTO_CREATEJOB GO CREATE PROCEDURE DBO.SP_AUTO_CREATEJOB @DBNAME VARCHAR (100), --执行数据库 @JOBNAME VARCHAR (100), @EXECSQL NVARCHAR(4000) --执行语句 AS PRINT '----作业 :' +@JOBNAME PRINT '----开始执行:' +@EXECSQL BEGIN TRANSACTION DECLARE @JOBID UNIQUEIDENTIFIER, @RETURNCODE INT SELECT @RETURNCODE = 0 BEGIN IF EXISTS ( SELECT JOB_ID FROM MSDB.DBO.SYSJOBS_VIEW WHERE NAME =@JOBNAME) EXECUTE MSDB.DBO.SP_DELETE_JOB @JOB_NAME=@JOBNAME EXECUTE @RETURNCODE = MSDB.DBO.SP_ADD_JOB --返回值:0成功,1失败 @JOB_NAME = @JOBNAME, @ENABLED = 1, --状态。默认值为 1(启用),为 0 不启用 @OWNER_LOGIN_NAME = 'SA' , --拥有作业的登录名。默认值为 NULL,可解释为当前登录名 @JOB_ID = @JOBID OUTPUT IF (@@ERROR <> 0 OR @RETURNCODE <> 0) GOTO QUITWITHROLLBACK EXECUTE @RETURNCODE = MSDB.DBO.SP_ADD_JOBSTEP @JOB_ID = @JOBID, @STEP_NAME = N 'STEP1' , --步骤名称 @SUBSYSTEM = 'TSQL' , --步骤的类型,一般为TSQL @DATABASE_NAME = @DBNAME, --执行数据库 @COMMAND = @EXECSQL --执行语句 IF (@@ERROR <> 0 OR @RETURNCODE <> 0) GOTO QUITWITHROLLBACK EXECUTE @RETURNCODE = MSDB.DBO.SP_UPDATE_JOB @JOB_ID = @JOBID, @START_STEP_ID = 1 --作业中要执行的第一个步骤的标识号 IF (@@ERROR <> 0 OR @RETURNCODE <> 0) GOTO QUITWITHROLLBACK EXECUTE @RETURNCODE = MSDB.DBO.SP_ADD_JOBSERVER @JOB_ID = @JOBID, @SERVER_NAME = N '(LOCAL)' IF (@@ERROR <> 0 OR @RETURNCODE <> 0) GOTO QUITWITHROLLBACK END COMMIT TRANSACTION GOTO ENDSAVE QUITWITHROLLBACK: IF (@@TRANCOUNT > 0) BEGIN ROLLBACK TRANSACTION RETURN 1 END ENDSAVE: EXEC @RETURNCODE = MSDB.DBO.SP_START_JOB @JOB_ID = @JOBID --启动作业 RETURN @RETURNCODE |
创建作业的步骤:
创建作业的步骤一般如下所示:
- 执行 sp_add_job 来创建作业。
-
执行 sp_add_jobstep 来创建一个或多个作业步骤。
-
执行 sp_add_schedule 来创建计划。
-
执行 sp_attach_schedule 将计划附加到作业。
-
执行 sp_add_jobserver 来设置作业的服务器。
作业系统表:
SQL SERVER 作业浅析
摘要: 作业介绍 SQL SERVER的作业是一系列由SQL SERVER代理按顺序执行的指定操作。作业可以执行一系列活动,包括运行Transact-SQL脚本、命令行应用程序、Microsoft ActiveX脚本、Integration Services 包、Analysis Services 命令和查询或复制任务。
作业介绍
SQL SERVER的作业是一系列由SQL SERVER代理按顺序执行的指定操作。作业可以执行一系列活动,包括运行Transact-SQL脚本、命令行应用程序、Microsoft ActiveX脚本、Integration Services 包、Analysis Services 命令和查询或复制任务。作业可以运行重复任务或那些可计划的任务,它们可以通过生成警报来自动通知用户作业状态,从而极大地简化了 SQL Server 管理[参见MSDN]。
创建作业、删除作业、查看作业历史记录....等所有操作都可以通过SSMS管理工具GUI界面操作,有时候也确实挺方便的。但是当一个实例有多个作业或 多个数据库实例时,通过图形化的界面去管理、维护作业也是个头痛的问题,对于SQL脚本与GUI界面管理维护作业熟优熟劣这个问题,只能说要看场合。下面 主要介绍通过SQL脚本来管理、维护作业。
作业分类
创建作业时,往往需要指定作业类别,如果不指定新建作业类别,就会默认为“[未分类(本地)]”,如下截图所示:
当然,你可以查看、添加、删除、修改作业分类。请看下面操作。
1:查看作业分类
- --method 1:
- EXEC msdb.dbo.sp_help_category;
- GO
- --method 2:
- SELECT category_id ,--作业类别ID
- category_class ,--类别中项目类型:1=作业2=警报 3=操作员
- category_type ,--类别中类型:=本地、=多服务器、=无
- name --分类名称
- FROMmsdb.dbo.syscategories
有兴趣的可以研究一下存储过程msdb.dbo.sp_help_category
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_help_category
@class VARCHAR(8) = 'JOB',
@type VARCHAR(12) = NULL,
@name sysname = NULL,
@suffix BIT = 0
AS
BEGIN
DECLARE @retval INT
DECLARE @type_in VARCHAR(12)
DECLARE @category_type INT
DECLARE @category_class INT
DECLARE @where_clause NVARCHAR(255)
DECLARE @cmd NVARCHAR(255)
SET NOCOUNT ON
SELECT @class = LTRIM(RTRIM(@class))
SELECT @type = LTRIM(RTRIM(@type))
SELECT @name = LTRIM(RTRIM(@name))
IF (@type = '') SELECT @type = NULL
IF (@name = N'') SELECT @name = NULL
IF (@class = 'JOB') AND (@type IS NULL)
SELECT @type_in = 'LOCAL'
ELSE
IF (@class <> 'JOB') AND (@type IS NULL)
SELECT @type_in = 'NONE'
ELSE
SELECT @type_in = @type
EXECUTE @retval = sp_verify_category @class,
@type_in,
NULL,
@category_class OUTPUT,
@category_type OUTPUT
IF (@retval <> 0)
RETURN(1)
IF (@suffix <> 0)
SELECT @suffix = 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)
END
SELECT @where_clause = N'WHERE (category_class = ' + CONVERT(NVARCHAR, @category_class) + N') '
IF (@name IS NOT NULL)
SELECT @where_clause = @where_clause + N'AND (name = N' + QUOTENAME(@name, '''') + N') '
IF (@type IS NOT NULL)
SELECT @where_clause = @where_clause + N'AND (category_type = ' + CONVERT(NVARCHAR, @category_type) + N') '
SELECT @cmd = N'SELECT category_id, '
IF (@suffix = 1)
BEGIN
SELECT @cmd = @cmd + N'''category_type'' = '
SELECT @cmd = @cmd + N'CASE category_type '
SELECT @cmd = @cmd + N'WHEN 0 THEN ''NONE'' '
SELECT @cmd = @cmd + N'WHEN 1 THEN ''LOCAL'' '
SELECT @cmd = @cmd + N'WHEN 2 THEN ''MULTI-SERVER'' '
SELECT @cmd = @cmd + N'WHEN 3 THEN ''NONE'' '
SELECT @cmd = @cmd + N'ELSE FORMATMESSAGE(14205) '
SELECT @cmd = @cmd + N'END, '
END
ELSE
BEGIN
SELECT @cmd = @cmd + N'category_type, '
END
SELECT @cmd = @cmd + N'name '
SELECT @cmd = @cmd + N'FROM msdb.dbo.syscategories '
EXECUTE (@cmd + @where_clause + N'ORDER BY category_type, name')
RETURN(@@error)
END
GO
sp_help_category
2:添加作业分类
如下所示,添加一个叫"DBA_MONITORING"的作业分类
- EXEC msdb.dbo.sp_add_category
- @class=N'JOB',
- @type=N'LOCAL',
- @name=N'DBA_MONITORING' ;
- GO
- SELECT * FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING'
- category_id category_class category_type name
- ----------- -------------- ------------- -------------
- 102 1 1 DBA_MONITORING
有兴趣的可以研究一下存储过程msdb.dbo.sp_add_category
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_add_category
@class VARCHAR(8) = 'JOB',
@type VARCHAR(12) = 'LOCAL',
@name sysname
AS
BEGIN
DECLARE @retval INT
DECLARE @category_type INT
DECLARE @category_class INT
SET NOCOUNT ON
SELECT @class = LTRIM(RTRIM(@class))
SELECT @type = LTRIM(RTRIM(@type))
SELECT @name = LTRIM(RTRIM(@name))
EXECUTE @retval = sp_verify_category @class,
@type,
@name,
@category_class OUTPUT,
@category_type OUTPUT
IF (@retval <> 0)
RETURN(1)
IF (EXISTS (SELECT *
FROM msdb.dbo.syscategories
WHERE (category_class = @category_class)
AND (name = @name)))
BEGIN
RAISERROR(14261, -1, -1, '@name', @name)
RETURN(1)
END
INSERT INTO msdb.dbo.syscategories (category_class, category_type, name)
VALUES (@category_class, @category_type, @name)
RETURN(@@error)
END
GO
sp_add_category
3:删除作业分类
如下所示,删除一个叫"DBA_MONITORING" 的作业分类
- EXEC msdb.dbo.sp_delete_category
- @name = N'DBA_MONITORING',
- @class = N'JOB' ;
- GO
有兴趣的可以研究一下存储过程msdb.dbo.sp_delete_category
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_delete_category
@class VARCHAR(8),
@name sysname
AS
BEGIN
DECLARE @retval INT
DECLARE @category_id INT
DECLARE @category_class INT
DECLARE @category_type INT
SET NOCOUNT ON
SELECT @class = LTRIM(RTRIM(@class))
SELECT @name = LTRIM(RTRIM(@name))
EXECUTE @retval = sp_verify_category @class,
NULL,
NULL,
@category_class OUTPUT,
NULL
IF (@retval <> 0)
RETURN(1)
SELECT @category_id = category_id,
@category_type = category_type
FROM msdb.dbo.syscategories
WHERE (category_class = @category_class)
AND (name = @name)
IF (@category_id IS NULL)
BEGIN
RAISERROR(14262, -1, -1, '@name', @name)
RETURN(1)
END
IF (@category_id < 100)
BEGIN
RAISERROR(14276, -1, -1, @name, @class)
RETURN(1)
END
BEGIN TRANSACTION
UPDATE msdb.dbo.sysjobs
SET category_id = CASE @category_type
WHEN 1 THEN 0
WHEN 2 THEN 2
END
WHERE (category_id = @category_id)
UPDATE msdb.dbo.sysalerts
SET category_id = 98
WHERE (category_id = @category_id)
UPDATE msdb.dbo.sysoperators
SET category_id = 99
WHERE (category_id = @category_id)
DELETE FROM msdb.dbo.syscategories
WHERE (category_id = @category_id)
COMMIT TRANSACTION
RETURN(0)
END
GO
sp_delete_category
4:修改作业类别
msdb.dbo.sp_update_category
[@class =] 'class' ,
[@name =] 'old_name' ,
[@new_name =] 'new_name'
|
有兴趣的可以研究一下存储过程msdb.dbo.sp_update_category
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_update_category
@class VARCHAR(8),
@name sysname,
@new_name sysname
AS
BEGIN
DECLARE @retval INT
DECLARE @category_id INT
DECLARE @category_class INT
SET NOCOUNT ON
SELECT @class = LTRIM(RTRIM(@class))
SELECT @name = LTRIM(RTRIM(@name))
SELECT @new_name = LTRIM(RTRIM(@new_name))
IF @name = '' SELECT @name = NULL
EXECUTE @retval = sp_verify_category @class,
NULL,
@new_name,
@category_class OUTPUT,
NULL
IF (@retval <> 0)
RETURN(1)
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)
END
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)
END
IF (@category_id < 100)
BEGIN
RAISERROR(14276, -1, -1, @name, @class)
RETURN(1)
END
UPDATE msdb.dbo.syscategories
SET name = @new_name
WHERE (category_class = @category_class)
AND (name = @name)
RETURN(@@error)
END
GO
sp_update_category
分析上面四个存储过程可以看出,实质上新增、修改、删除、查看作业类别无非就是对表 msdb.dbo.syscategories进行操作,只是通过存储过程封装了而已,增加了验证等操作,确保数据完整性。
新建作业
创建作业的步骤一般如下所示:
- 执行 sp_add_job 来创建作业。
-
执行 sp_add_jobstep 来创建一个或多个作业步骤。
-
执行 sp_add_schedule 来创建计划。
-
执行 sp_attach_schedule 将计划附加到作业。
-
执行 sp_add_jobserver 来设置作业的服务器。
本地作业是由本地 SQL Server 代理进行缓存的。因此,任何修改都会隐式强制 SQL Server 代理重新缓存该作业。由于直到调用 sp_add_jobserver 时,SQL Server 代理才缓存作业,因此最后调用 sp_add_jobserver 将更为有效。
下面看用脚本新建一个作业用来每天执行exec sp_cycle_errorlog ,实现错误日志循环, 从下面的脚本量来看,用脚本新建一个作业确实工作量很大,而且容易出错,GUI图形界面创建作业要方便得多,但是如果迁移数据库时,用脚本来新建作业是相 当方便的。比GUI图形界面新建一个作业快捷方便多了。
- USE [msdb]
- GO
- /****** Object: Job [JOB_CYCLE_ERRORLOG] Script Date: 08/23/2013 15:25:09 ******/
- IFEXISTS(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'JOB_CYCLE_ERRORLOG')
- EXEC msdb.dbo.sp_delete_job@job_id=N'a5dff08b-95f8-498e-a6c9-59241fe197b4', @delete_unused_schedule=1
- GO
- USE [msdb]
- GO
- /****** Object: Job [JOB_CYCLE_ERRORLOG] Script Date: 08/23/2013 15:25:09 ******/
- BEGIN TRANSACTION
- DECLARE @ReturnCode INT
- 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=N'DBA_MATIANCE' AND category_class=1)
- BEGIN
- EXEC @ReturnCode = msdb.dbo.sp_add_category@class=N'JOB', @type=N'LOCAL', @name=N'DBA_MATIANCE'
- IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
- END
- DECLARE @jobId BINARY(16)
- EXEC @ReturnCode =msdb.dbo.sp_add_job@job_name=N'JOB_CYCLE_ERRORLOG',
- @enabled=1,
- @notify_level_eventlog=0,
- @notify_level_email=0,
- @notify_level_netsend=0,
- @notify_level_page=0,
- @delete_level=0,
- @description=N'每天执行exec sp_cycle_errorlog 实现错误日志循环。',
- @category_name=N'DBA_MATIANCE',
- @owner_login_name=N'sa', @job_id = @jobId OUTPUT
- IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
- /****** 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=N'Step 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=N'TSQL',
- @command=N'exec msdb.dbo.sp_cycle_errorlog',
- @database_name=N'msdb',
- @flags=0
- IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
- EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
- IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
- EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Job Schedule',
- @enabled=1,
- @freq_type=4,
- @freq_interval=1,
- @freq_subday_type=1,
- @freq_subday_interval=0,
- @freq_relative_interval=0,
- @freq_recurrence_factor=0,
- @active_start_date=20130823,
- @active_end_date=99991231,
- @active_start_time=0,
- @active_end_time=235959,
- @schedule_uid=N'2099c694-cd26-4edf-8803-179227bf8770'
- IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
- EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
- IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
- COMMIT TRANSACTION
- GOTO EndSave
- QuitWithRollback:
- IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
- EndSave:
- GO
作业系统表
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 命令启动作业:
语法:
1 2 3 4 5 6 7 8 9 10 11 | 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 2 3 4 5 6 7 8 9 | 语法: 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' |
启用和禁用作业:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 语法: 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 = N 'JOB_CYCLE_ERRORLOG' , @enabled = 0 ; --0 禁用作业、 1启用作业 GO |
删除作业:
1 2 3 4 | sp_delete_job { [ @job_id = ] job_id | [ @job_name = ] 'job_name' } ,<br> [ , [ @originating_server = ] 'server' ]<br> [ , [ @delete_history = ] delete_history ]<br> [ , [ @delete_unused_schedule = ] delete_unused_schedule ] 例子: EXEC msdb.dbo.sp_delete_job @job_name = 'JOB_CYCLE_ERRORLOG' ; |
常用管理作业SQL:
1:查看属于某个数据库的所有作业。
1 2 3 4 5 6 7 8 9 10 | SELECT j.job_id AS JOB_ID , name AS JOB_NAME , enabled AS JOB_ENABLED , description AS JOB_DESCRIPTION , date_created AS DATE_CREATED , date_modified AS DATE_MODIFIED FROM msdb.dbo.sysjobs j WHERE job_id IN ( SELECT job_id FROM msdb.dbo.sysjobsteps WHERE database_name = 'DataBaseName' ) |
2:查看某个作业类别的所有作业
1 2 3 4 5 6 7 8 | SELECT j. name AS Job_Name , j.description AS Job_Description , j.date_created AS Date_Created , j.date_modified AS Date_Modified , c. name AS Job_Class FROM msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id WHEREc. name = '[Uncategorized (Local)]' |
3:查看禁用/启用的作业
1 | SELECT * FROM msdb.dbo.sysjobs WHERE enabled=0 --0:禁用 1:为启用 |
4:查看出错的作业记录
4.1:查询那些作业在今天出错(如果要查询历史出错作业,去掉查询时间条件即可)
1 2 3 4 5 6 7 8 9 10 11 | SELECT name AS JOB_NAME , description AS JOB_Description , date_created AS Date_Created , date_modified AS Date_Modified FROM msdb.dbo.sysjobs WHERE enabled = 1 AND job_id IN ( SELECT job_id FROM Msdb.dbo.sysjobhistory WHERE run_status = 0 AND run_date = CAST ( CONVERT ( VARCHAR (8), GETDATE(), 112) AS INT ) ) |
4.2:查看出错详细信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT j. name AS JOB_NAME , h.step_id AS STEP_ID , h.step_name AS STEP_NAME, h.message AS ERR_MSG , h.run_date AS RUN_DATE , h.run_time AS RUN_TIME , msdb.dbo.agent_datetime(h.run_date, h.run_time) AS 'RunDateTime' , CAST (run_duration / 10000 AS VARCHAR (2)) + N '小时' + CAST (( run_duration - run_duration / 10000 * 10000 ) / 100 AS VARCHAR (2)) + N '分钟' + SUBSTRING ( CAST (run_duration AS VARCHAR (10)), LEN( CAST (run_duration AS VARCHAR (10))) - 1, 2) + N '秒' AS run_duration FROM msdb.dbo.sysjobhistory h LEFT JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id WHERE run_status = 0 AND run_date = CAST ( CONVERT ( VARCHAR (8), GETDATE(), 112) AS INT ) |
5:查看作业的执行时间:
5.1:查看当天成功执行的作业的时间(查看的是作业Step信息)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT j. name AS job_name , h.step_id AS step_id , h.step_name AS step_name, h.message AS Message , h.run_date AS Run_date , h.run_time AS run_time , msdb.dbo.agent_datetime(h.run_date, h.run_time) AS 'RunDateTime' , CAST (run_duration / 10000 AS VARCHAR (2)) + N '小时' + CAST (( run_duration - run_duration / 10000 * 10000 ) / 100 AS VARCHAR (2)) + N '分钟' + SUBSTRING ( CAST (run_duration AS VARCHAR (10)), LEN( CAST (run_duration AS VARCHAR (10))) - 1, 2) + N '秒' AS run_duration FROM msdb.dbo.sysjobhistory h LEFT JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id WHERE run_status = 1 AND run_date = CAST ( CONVERT ( VARCHAR (8), GETDATE(), 112) AS INT ) ORDER BY run_duration DESC |
5.2:查询每个作业的执行时间、按执行时间降序
1 2 3 4 5 6 7 8 9 10 | SELECT j. name AS JOB_NAME , h.run_date AS RUN_DATE , SUM (run_duration) AS SUM_DURATION FROM msdb.dbo.sysjobhistory h LEFT JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id WHERE run_status = 1 AND run_date = CAST ( CONVERT ( VARCHAR (8), GETDATE(), 112) AS INT ) GROUP BY name , run_date ORDER BY Sum_Duration DESC |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 智能桌面机器人:用.NET IoT库控制舵机并多方法播放表情
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· 新年开篇:在本地部署DeepSeek大模型实现联网增强的AI应用
· 程序员常用高效实用工具推荐,办公效率提升利器!
· Janus Pro:DeepSeek 开源革新,多模态 AI 的未来
· 【译】WinForms:分析一下(我用 Visual Basic 写的)