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：查看作业分类

EXEC msdb.dbo.sp_help_category;
 
GO
 
--method 2:
  SELECT  category_id            ,--作业类别ID
          category_class         ,--类别中项目类型：1=作业2=警报 3=操作员
          category_type          ,--类别中类型：=本地、=多服务器、=无
          name                    --分类名称
    FROMmsdb.dbo.syscategories

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

3：删除作业分类

如下所示，删除一个叫"DBA_MONITORING" 的作业分类

EXEC msdb.dbo.sp_delete_category
    @name = N'DBA_MONITORING',
    @class = N'JOB' ;
 
GO

4：修改作业类别

 

msdb.dbo.sp_update_category      [@class =] 'class' ,      [@name =] 'old_name' ,      [@new_name =] 'new_name'

新建作业

 

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

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图形界面新建一个作业快捷方便多了。

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