绑定完请刷新页面
取消
刷新
登录 | 注册

分享好友

×
取消 复制
SQL SERVER 作业浅析
2022-07-27 17:21:55

作业介绍

    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

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

sp_delete_category

4：修改作业类别

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

有兴趣的可以研究一下存储过程msdb.dbo.sp_update_category

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

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 = 
/****** 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 <>  OR @ReturnCode <> ) 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=,
        @notify_level_email=,
        @notify_level_netsend=,
        @notify_level_page=,
        @delete_level=,
        @description=N'每天执行exec sp_cycle_errorlog 实现错误日志循环。',
        @category_name=N'DBA_MATIANCE',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <>  OR @ReturnCode <> ) 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=,
        @on_success_action=1,
        @on_success_step_id=,
        @on_fail_action=2,
        @on_fail_step_id=,
        @retry_attempts=,
        @retry_interval=,
        @os_run_priority=, @subsystem=N'TSQL',
        @command=N'exec msdb.dbo.sp_cycle_errorlog',
        @database_name=N'msdb',
        @flags=
IF (@@ERROR <>  OR @ReturnCode <> ) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <>  OR @ReturnCode <> ) 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=,
        @freq_relative_interval=,
        @freq_recurrence_factor=,
        @active_start_date=20130823,
        @active_end_date=99991231,
        @active_start_time=,
        @active_end_time=235959,
        @schedule_uid=N'2099c694-cd26-4edf-8803-179227bf8770'
IF (@@ERROR <>  OR @ReturnCode <> ) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <>  OR @ReturnCode <> ) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > ) ROLLBACK TRANSACTION
EndSave:
 
GO

作业系统表

    与作业有关的系统表、视图大致有下面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'

启用或禁用作业

 

        1：通过SSMS工具启用作业[参见MSDN]

        2：通过SQL命令禁用作业

语法:
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 =  ;  -- 禁用作业、  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 编写作业脚本

  1. 在对象资源管理器中，连接到 Microsoft SQL Server 数据库引擎实例，再展开该实例。

  2. 展开“SQL Server 代理”，再展开“作业”，然后右键单击要编写脚本的作业。

  3. 从快捷菜单中，选择“编写作业脚本为”，再选择“CREATE 到”或“DROP 到”，并单击下列内容之一：

    新查询编辑器窗口，将打开一个新的查询编辑器窗口，并为其编写 Transact-SQL 脚本。

    文件，将 Transact-SQL 脚本保存到文件。

      剪贴板，将 Transact-SQL 脚本保存到剪贴板

 

常用管理作业SQL

 

1:查看属于某个数据库的所有作业。

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( SELECTjob_id
                  FROM    msdb.dbo.sysjobsteps
                  WHERE   database_name = 'DataBaseName' )

2:查看某个作业类别的所有作业

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:查看禁用/启用的作业

SELECT * FROM msdb.dbo.sysjobs WHERE  enabled=0    --0：禁用 1：为启用

 

4:查看出错的作业记录

 

4.1：查询那些作业在今天出错（如果要查询历史出错作业，去掉查询时间条件即可）

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 = 
              AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT) )

4.2：查看出错详细信息

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 = 
      AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)

5：查看作业的执行时间:

5.1:查看当天成功执行的作业的时间（查看的是作业Step信息）

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：查询每个作业的执行时间、按执行时间降序

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

参考资料：

http://blog.csdn.net/dba_huangzj/article/details/8300178


本文来源https://www.cnblogs.com/kerrycode/p/3279559.html

上一篇：Linux 下 ls 和 du 计算的文件差别那么大？排查后结果竟是这样
下一篇：SQL Server死锁总结
分享好友

分享这个小栈给你的朋友们，一起进步吧。

Microsoft SQL Server
创建时间：2022-03-30 11:29:11
Microsoft SQL Server
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询

• 专区发布评论属默认订阅所评论专区（除付费小栈外）

技术专家

查看更多
  • itt0918
    专家
戳我，来吐槽～

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8  北京市公安局海淀分局网监中心备案编号：11010802021510  未成年人举报专区

违法不良信息举报电话:18101376593   广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员