mssql 作业

1.什么叫作业？

作业是一系列由 SQL Server 代理按顺序执行的指定操作。 一个作业可以执行各种类型的活动，包括运行 Transact-SQL 脚本、命令提示符应用程序、Microsoft ActiveX 脚本、Integration Services 包、Analysis Services 命令和查询或复制任务。 作业可以运行重复或可计划的任务，然后它们可以通过生

成警报来自动通知用户作业状态，从而极大地简化了 SQL Server 管理。

2.先看一个常用通过GUI利用作业动态备份数据据库。

    1、打开SQL Server Management Studio
    2、启动SQL Server代理
    3、点击作业->新建作业
    4、"常规"中输入作业的名称
    5、新建步骤，类型选T-SQL，在下面的命令中输入下面语句
     DECLARE @strPath NVARCHAR(200) 
       set @strPath = convert(NVARCHAR(20),getdate(),120) 
       set @strPath = REPLACE(@strPath, ':' , '.') 
       set @strPath = 'E:\DATA_db\MSSQL.1\MSSQL\Backup' + @strPath + '.bak' 
       BACKUP DATABASE [数据库名] TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT 
    6、添加计划，设置频率，时间等。基本把以上的流程基本的填写完，就可以正常作业了。

3.作业系统表

    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 用来组织作业、警报和操作员的类别

4.创建作业

　创建作业的步骤一般如下所示：
　　执行 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 将更为有效。

启动作业

        1：通过SSMS工具启动作业[略]

        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工具停作业[略]

        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工具启用作业[略]

        2：通过SQL命令禁用作业

语法:

列子：EXEC msdb.dbo.sp_update_job

    @job_name = N‘JOB_CYCLE_ERRORLOG‘,

    @enabled = 0 ;  --0 禁用作业、  1启用作业

GO

删除作业

1：通过SSMS工具删除作业[略]

2：通过SQL命令删除作业

例子：

EXEC msdb.dbo.sp_delete_job  @job_name = ‘JOB_CYCLE_ERRORLOG‘;

　　--1:查看属于某个数据库的所有作业。
　　SELECT job.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 job
　　WHERE   job_id IN( SELECTjob_id
　　                  FROM    msdb.dbo.sysjobsteps
　　                  WHERE   database_name = 'DataBaseName' )
　　 --2:查看某个作业类别的所有作业
　　SELECT　　job.name             AS Job_Name       ,
　　         job.description      AS Job_Description ,
　　         job.date_created     AS Date_Created    ,
　　         job.date_modified    AS Date_Modified   ,
　　         type .name             AS Job_Class
　　FROM  msdb.dbo.sysjobs job
　　      LEFT JOIN msdb.dbo.syscategories type ON job.category_id = type .category_id
　　 WHERE type.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 = 0
　　              AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT) )
　　 
　　--4.2：查看出错详细信息
　　SELECT job.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 job ON h.job_id = job.job_id
　　WHERE run_status = 0
　　      AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)
　　 
　　--5：查看作业的执行时间:
　　--5.1:查看当天成功执行的作业的时间（查看的是作业Step信息）
　　SELECT job.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 job ON h.job_id = job.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  job.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 job ON h.job_id = job.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