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   

 

创建作业的步骤:

创建作业的步骤一般如下所示:

  1. 执行 sp_add_job 来创建作业。
  2. 执行 sp_add_jobstep 来创建一个或多个作业步骤。

  3. 执行 sp_add_schedule 来创建计划。

  4. 执行 sp_attach_schedule 将计划附加到作业。

  5. 执行 sp_add_jobserver 来设置作业的服务器。

 

作业系统表:

  1. 云栖社区>
  2. 博客列表>
  3. 正文

SQL SERVER 作业浅析

 
潇湘隐者 2016-04-25 14:53:24 浏览226 评论0

摘要: 作业介绍     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脚本来管理、维护作业。

作业分类

    创建作业时,往往需要指定作业类别,如果不指定新建作业类别,就会默认为“[未分类(本地)]”,如下截图所示:

clipboard

 

    当然,你可以查看、添加、删除、修改作业分类。请看下面操作。

1:查看作业分类

Code Snippet
  1. --method 1:
  2. EXEC msdb.dbo.sp_help_category;
  3.  
  4. GO
  5.  
  6. --method 2:
  7.   SELECT  category_id            ,--作业类别ID
  8.           category_class         ,--类别中项目类型:1=作业2=警报 3=操作员
  9.           category_type          ,--类别中类型:=本地、=多服务器、=无
  10.           name                    --分类名称
  11.     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"的作业分类

Code Snippet
  1. EXEC msdb.dbo.sp_add_category
  2.     @class=N'JOB',
  3.     @type=N'LOCAL',
  4.     @name=N'DBA_MONITORING' ;
  5.  
  6. GO
  7.  
  8.   
  9.  
  10. SELECT * FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING'
  11.  
  12.  category_id category_class category_type    name
  13. ----------- -------------- ------------- -------------
  14. 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" 的作业分类

Code Snippet
  1. EXEC msdb.dbo.sp_delete_category
  2.     @name = N'DBA_MONITORING',
  3.     @class = N'JOB' ;
  4.  
  5. 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进行操作,只是通过存储过程封装了而已,增加了验证等操作,确保数据完整性。

新建作业

 

创建作业的步骤一般如下所示:

  1. 执行 sp_add_job 来创建作业。
  2. 执行 sp_add_jobstep 来创建一个或多个作业步骤。

  3. 执行 sp_add_schedule 来创建计划。

  4. 执行 sp_attach_schedule 将计划附加到作业。

  5. 执行 sp_add_jobserver 来设置作业的服务器。

本地作业是由本地 SQL Server 代理进行缓存的。因此,任何修改都会隐式强制 SQL Server 代理重新缓存该作业。由于直到调用 sp_add_jobserver 时,SQL Server 代理才缓存作业,因此最后调用 sp_add_jobserver 将更为有效。

下面看用脚本新建一个作业用来每天执行exec sp_cycle_errorlog ,实现错误日志循环, 从下面的脚本量来看,用脚本新建一个作业确实工作量很大,而且容易出错,GUI图形界面创建作业要方便得多,但是如果迁移数据库时,用脚本来新建作业是相 当方便的。比GUI图形界面新建一个作业快捷方便多了。

Code Snippet
  1. USE [msdb]
  2. GO
  3.  
  4. /****** Object:  Job [JOB_CYCLE_ERRORLOG]    Script Date: 08/23/2013 15:25:09 ******/
  5.   IFEXISTS(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'JOB_CYCLE_ERRORLOG')
  6. EXEC msdb.dbo.sp_delete_job@job_id=N'a5dff08b-95f8-498e-a6c9-59241fe197b4', @delete_unused_schedule=1
  7. GO
  8.  
  9. USE [msdb]
  10. GO
  11.  
  12. /****** Object:  Job [JOB_CYCLE_ERRORLOG]    Script Date: 08/23/2013 15:25:09 ******/
  13. BEGIN TRANSACTION
  14. DECLARE @ReturnCode INT
  15. SELECT @ReturnCode = 0
  16. /****** Object:  JobCategory [DBA_MATIANCE]    Script Date: 08/23/2013 15:25:09 ******/
  17. IF NOT EXISTS(SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA_MATIANCE' AND category_class=1)
  18. BEGIN
  19. EXEC @ReturnCode = msdb.dbo.sp_add_category@class=N'JOB', @type=N'LOCAL', @name=N'DBA_MATIANCE'
  20. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  21.  
  22. END
  23.  
  24. DECLARE @jobId BINARY(16)
  25.   EXEC @ReturnCode =msdb.dbo.sp_add_job@job_name=N'JOB_CYCLE_ERRORLOG',
  26.         @enabled=1,
  27.         @notify_level_eventlog=0,
  28.         @notify_level_email=0,
  29.         @notify_level_netsend=0,
  30.         @notify_level_page=0,
  31.         @delete_level=0,
  32.         @description=N'每天执行exec sp_cycle_errorlog 实现错误日志循环。',
  33.         @category_name=N'DBA_MATIANCE',
  34.         @owner_login_name=N'sa', @job_id = @jobId OUTPUT
  35. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  36. /****** Object:  Step [Step 1: recycle the errorlog]    Script Date: 08/23/2013 15:25:09 ******/
  37. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep@job_id=@jobId, @step_name=N'Step 1: recycle the errorlog',
  38.         @step_id=1,
  39.         @cmdexec_success_code=0,
  40.         @on_success_action=1,
  41.         @on_success_step_id=0,
  42.         @on_fail_action=2,
  43.         @on_fail_step_id=0,
  44.         @retry_attempts=0,
  45.         @retry_interval=0,
  46.         @os_run_priority=0, @subsystem=N'TSQL',
  47.         @command=N'exec msdb.dbo.sp_cycle_errorlog',
  48.         @database_name=N'msdb',
  49.         @flags=0
  50. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  51. EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
  52. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  53. EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Job Schedule',
  54.         @enabled=1,
  55.         @freq_type=4,
  56.         @freq_interval=1,
  57.         @freq_subday_type=1,
  58.         @freq_subday_interval=0,
  59.         @freq_relative_interval=0,
  60.         @freq_recurrence_factor=0,
  61.         @active_start_date=20130823,
  62.         @active_end_date=99991231,
  63.         @active_start_time=0,
  64.         @active_end_time=235959,
  65.         @schedule_uid=N'2099c694-cd26-4edf-8803-179227bf8770'
  66. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  67. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
  68. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  69. COMMIT TRANSACTION
  70. GOTO EndSave
  71. QuitWithRollback:
  72.     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
  73. EndSave:
  74.  
  75. 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

 

 

 

 

posted @   石shi  阅读(4500)  评论(0编辑  收藏  举报
编辑推荐:
· 智能桌面机器人:用.NET IoT库控制舵机并多方法播放表情
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
阅读排行:
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· 新年开篇:在本地部署DeepSeek大模型实现联网增强的AI应用
· 程序员常用高效实用工具推荐,办公效率提升利器!
· Janus Pro:DeepSeek 开源革新,多模态 AI 的未来
· 【译】WinForms:分析一下(我用 Visual Basic 写的)
点击右上角即可分享
微信分享提示