SQL SERVER 中如何用脚本管理作业

DECLARE @WeekDays TABLE (     freq_interval    INT,     weekdays        NVARCHAR(120)   )   INSERT INTO @WeekDays SELECT 1    ,N'星期日               '  UNION ALL SELECT 2    ,N'星期一               '  UNION ALL SELECT 4    ,N'星期二               '  UNION ALL SELECT 8    ,N'星期三               '  UNION ALL SELECT 16   ,N'星期四               '  UNION ALL SELECT 32   ,N'星期五               '  UNION ALL SELECT 64   ,N'星期六               '  UNION ALL SELECT 3    ,N'星期日,一            '  UNION ALL SELECT 5    ,N'星期日,二            '  UNION ALL SELECT 9    ,N'星期日,三            '  UNION ALL SELECT 17   ,N'星期日,四            '  UNION ALL SELECT 33   ,N'星期日,五            '  UNION ALL SELECT 65   ,N'星期日,六            '  UNION ALL SELECT 6    ,N'星期一,二            '  UNION ALL SELECT 10   ,N'星期一,三            '  UNION ALL SELECT 18   ,N'星期一,四            '  UNION ALL SELECT 34   ,N'星期一,五            '  UNION ALL SELECT 66   ,N'星期一,六            '  UNION ALL SELECT 12   ,N'星期二,三            '  UNION ALL SELECT 20   ,N'星期二,四            '  UNION ALL SELECT 36   ,N'星期二,五            '  UNION ALL SELECT 68   ,N'星期二,六            '  UNION ALL SELECT 24   ,N'星期三,四            '  UNION ALL SELECT 40   ,N'星期三,五            '  UNION ALL SELECT 72   ,N'星期三,六            '  UNION ALL SELECT 48   ,N'星期四,五            '  UNION ALL SELECT 80   ,N'星期四,六            '  UNION ALL SELECT 96   ,N'星期五,六            '  UNION ALL SELECT 7    ,N'星期日,一,二         '  UNION ALL SELECT 11   ,N'星期日,一,三         '  UNION ALL SELECT 19   ,N'星期日,一,四         '  UNION ALL SELECT 35   ,N'星期日,一,五         '  UNION ALL SELECT 67   ,N'星期日,一,六         '  UNION ALL SELECT 13   ,N'星期日,二,三         '  UNION ALL SELECT 21   ,N'星期日,二,四         '  UNION ALL SELECT 37   ,N'星期日,二,五         '  UNION ALL SELECT 69   ,N'星期日,二,六         '  UNION ALL SELECT 25   ,N'星期日,三,四         '  UNION ALL SELECT 41   ,N'星期日,三,五         '  UNION ALL SELECT 73   ,N'星期日,三,六         '  UNION ALL SELECT 49   ,N'星期日,四,五         '  UNION ALL SELECT 81   ,N'星期日,四,六         '  UNION ALL SELECT 97   ,N'星期日,五,六         '  UNION ALL SELECT 14   ,N'星期一,二,三         '  UNION ALL SELECT 22   ,N'星期一,二,四         '  UNION ALL SELECT 38   ,N'星期一,二,五         '  UNION ALL SELECT 70   ,N'星期一,二,六         '  UNION ALL SELECT 26   ,N'星期一,三,四         '  UNION ALL SELECT 42   ,N'星期一,三,五         '  UNION ALL SELECT 74   ,N'星期一,三,六         '  UNION ALL SELECT 50   ,N'星期一,四,五         '  UNION ALL SELECT 82   ,N'星期一,四,六         '  UNION ALL SELECT 98   ,N'星期一,五,六         '  UNION ALL SELECT 28   ,N'星期二,三,四         '  UNION ALL SELECT 44   ,N'星期二,三,五         '  UNION ALL SELECT 76   ,N'星期二,三,六         '  UNION ALL SELECT 52   ,N'星期二,四,五         '  UNION ALL SELECT 84   ,N'星期二,四,六         '  UNION ALL SELECT 100  ,N'星期二,五,六         '  UNION ALL SELECT 56   ,N'星期三,四,五         '  UNION ALL SELECT 88   ,N'星期三,四,六         '  UNION ALL SELECT 104  ,N'星期三,五,六         '  UNION ALL SELECT 112  ,N'星期四,五,六         '  UNION ALL SELECT 15   ,N'星期日,一,二,三      '  UNION ALL SELECT 23   ,N'星期日,一,二,四      '  UNION ALL SELECT 39   ,N'星期日,一,二,五      '  UNION ALL SELECT 71   ,N'星期日,一,二,六      '  UNION ALL SELECT 27   ,N'星期日,一,三,四      '  UNION ALL SELECT 43   ,N'星期日,一,三,五      '  UNION ALL SELECT 75   ,N'星期日,一,三,六      '  UNION ALL SELECT 51   ,N'星期日,一,四,五      '  UNION ALL SELECT 83   ,N'星期日,一,四,六      '  UNION ALL SELECT 99   ,N'星期日,一,五,六      '  UNION ALL SELECT 29   ,N'星期日,二,三,四      '  UNION ALL SELECT 45   ,N'星期日,二,三,五      '  UNION ALL SELECT 77   ,N'星期日,二,三,六      '  UNION ALL SELECT 53   ,N'星期日,二,四,五      '  UNION ALL SELECT 85   ,N'星期日,二,四,六      '  UNION ALL SELECT 101  ,N'星期日,二,五,六      '  UNION ALL SELECT 57   ,N'星期日,三,四,五      '  UNION ALL SELECT 89   ,N'星期日,三,四,六      '  UNION ALL SELECT 105  ,N'星期日,三,五,六      '  UNION ALL SELECT 113  ,N'星期日,四,五,六      '  UNION ALL SELECT 30   ,N'星期一,二,三,四      '  UNION ALL SELECT 46   ,N'星期一,二,三,五      '  UNION ALL SELECT 78   ,N'星期一,二,三,六      '  UNION ALL SELECT 54   ,N'星期一,二,四,五      '  UNION ALL SELECT 86   ,N'星期一,二,四,六      '  UNION ALL SELECT 102  ,N'星期一,二,五,六      '  UNION ALL SELECT 58   ,N'星期一,三,四,五      '  UNION ALL SELECT 90   ,N'星期一,三,四,六      '  UNION ALL SELECT 106  ,N'星期一,三,五,六      '  UNION ALL SELECT 114  ,N'星期一,四,五,六      '  UNION ALL SELECT 60   ,N'星期二,三,四,五      '  UNION ALL SELECT 92   ,N'星期二,三,四,六      '  UNION ALL SELECT 108  ,N'星期二,三,五,六      '  UNION ALL SELECT 116  ,N'星期二,四,五,六      '  UNION ALL SELECT 120  ,N'星期三,四,五,六      '  UNION ALL SELECT 31   ,N'星期日,一,二,三,四   '  UNION ALL SELECT 47   ,N'星期日,一,二,三,五   '  UNION ALL SELECT 79   ,N'星期日,一,二,三,六   '  UNION ALL SELECT 55   ,N'星期日,一,二,四,五   '  UNION ALL SELECT 87   ,N'星期日,一,二,四,六   '  UNION ALL SELECT 103  ,N'星期日,一,二,五,六   '  UNION ALL SELECT 59   ,N'星期日,一,三,四,五   '  UNION ALL SELECT 91   ,N'星期日,一,三,四,六   '  UNION ALL SELECT 107  ,N'星期日,一,三,五,六   '  UNION ALL SELECT 115  ,N'星期日,一,四,五,六   '  UNION ALL SELECT 61   ,N'星期日,二,三,四,五   '  UNION ALL SELECT 93   ,N'星期日,二,三,四,六   '  UNION ALL SELECT 109  ,N'星期日,二,三,五,六   '  UNION ALL SELECT 117  ,N'星期日,二,四,五,六   '  UNION ALL SELECT 121  ,N'星期日,三,四,五,六   '  UNION ALL SELECT 62   ,N'星期一,二,三,四,五   '  UNION ALL SELECT 94   ,N'星期一,二,三,四,六   '  UNION ALL SELECT 110  ,N'星期一,二,三,五,六   '  UNION ALL SELECT 118  ,N'星期一,二,四,五,六   '  UNION ALL SELECT 122  ,N'星期一,三,四,五,六   '  UNION ALL SELECT 124  ,N'星期二,三,四,五,六   '  UNION ALL SELECT 63   ,N'星期日,一,二,三,四,五'  UNION ALL SELECT 95   ,N'星期日,一,二,三,四,六'  UNION ALL SELECT 111  ,N'星期日,一,二,三,五,六'  UNION ALL SELECT 119  ,N'星期日,一,二,四,五,六'  UNION ALL SELECT 123  ,N'星期日,一,三,四,五,六'  UNION ALL SELECT 125  ,N'星期日,二,三,四,五,六'  UNION ALL SELECT 126  ,N'星期一,二,三,四,五,六'  UNION ALL SELECT 127  ,N'星期日,一,二,三,四,五,六';         SELECT  d.job_id                                    AS job_id    ,         j.name                                        AS job_name ,         CASE WHEN j.enabled =1  THEN N'启用'               ELSE N'禁用' END                        AS job_staus,         CASE WHEN freq_type=1    THEN N'运行一次'              WHEN freq_type=4    THEN N'每天执行'              WHEN freq_type=8    THEN N'每周执行'              WHEN freq_type=16    THEN N'每月执行'              WHEN freq_type=32    THEN N'每月执行'              WHEN freq_type=64    THEN N'代理服务启动时运行'              WHEN freq_type=128 THEN N'在计算机空闲时运行'         END                                            AS  freq_type,         CASE WHEN freq_type=1    THEN  N'选项无意义'              WHEN freq_type=4    THEN                  (                  CASE WHEN freq_subday_type=4                            THEN  N'每隔' +  CONVERT(NVARCHAR(4),freq_subday_interval) +N'分钟执行一次'                   WHEN freq_subday_type=8                        THEN  N'每隔' +  CONVERT(NVARCHAR(4),freq_subday_interval) +N'小时执行一次'                  ELSE         N'每天执行'+ CONVERT(NVARCHAR(4),freq_interval)  + N'次'                   END                  )              WHEN freq_type=8    THEN                  ( SELECT w.weekdays FROM   @WeekDays w WHERE w.freq_interval= s.freq_interval                  )                                                   WHEN freq_type=16  THEN  N'每月' +  CONVERT(NVARCHAR(4),freq_interval)     + N'号执行'              WHEN freq_type=32  THEN  N'每月星期' + CONVERT(NVARCHAR(4),freq_interval)  + N'执行'         END AS freq_relative_interval,         CASE WHEN freq_subday_type =1 THEN N'指定时间点执行一次'               WHEN freq_subday_type =2 THEN N'每隔：' + CAST(freq_subday_interval AS VARCHAR(2)) + N'秒执行一次'              WHEN freq_subday_type =4 THEN N'每隔：' + CAST(freq_subday_interval AS VARCHAR(2)) + N'分执行一次'              WHEN freq_subday_type =8 THEN N'每隔：' + CAST(freq_subday_interval AS VARCHAR(2)) + N'小时执行一次'         END AS freq_subday_type,          CASE WHEN freq_subday_type =1 THEN N'开始时间点：'                      + CAST(active_start_time / 10000   AS VARCHAR(2)) + N'点'                     + CAST(active_start_time%10000/100 AS VARCHAR(2)) + N'分'              WHEN freq_subday_type =2 THEN N'开始时间点：'                      + CAST(active_start_time / 10000 AS VARCHAR(2)) + N'点'                     + CAST(active_start_time%10000/100 AS VARCHAR(2)) + N'分'              WHEN freq_subday_type =4 THEN N'开始时间点：'                      + CAST(active_start_time / 10000 AS VARCHAR(2))   + N'点'                     + CAST(active_start_time%10000/100 AS VARCHAR(2)) + N'分'              WHEN freq_subday_type =8 THEN N'开始时间点：'                     + CAST(active_start_time / 10000 AS VARCHAR(2))   + N'点'                     + CAST(active_start_time%10000/100 AS VARCHAR(2)) + N'分'         END AS job_start_time,          CASE WHEN freq_subday_type =1 THEN N'结束时间点：'                      + CAST(active_end_time / 10000   AS VARCHAR(2)) + N'点'                     + CAST(active_end_time%10000/100 AS VARCHAR(2)) + N'分'              WHEN freq_subday_type =2 THEN N'结束时间点：'                      + CAST(active_end_time / 10000 AS VARCHAR(2)) + N'点'                     + CAST(active_end_time%10000/100 AS VARCHAR(2)) + N'分'              WHEN freq_subday_type =4 THEN N'结束时间点：'                      + CAST(active_end_time / 10000 AS VARCHAR(2))   + N'点'                     + CAST(active_end_time%10000/100 AS VARCHAR(2)) + N'分'              WHEN freq_subday_type =8 THEN N'结束时间点：'                     + CAST(active_end_time / 10000 AS VARCHAR(2))   + N'点'                     + CAST(active_end_time%10000/100 AS VARCHAR(2)) + N'分'         END AS job_end_time,          freq_type,         freq_interval,              freq_subday_type,         freq_subday_interval,         active_start_date,         active_start_time FROM msdb.dbo.sysschedules s INNER JOIN msdb.dbo.sysjobschedules d ON s.schedule_id=d.schedule_id INNER JOIN msdb.dbo.sysjobs j ON d.job_id = j.job_id ORDER BY j.name 

--查看实例下被禁用的作业 SELECT j.job_id                                         AS JOB_ID                   ,j.name                                           AS JOB_NAME                 ,CASE WHEN [enabled] =1 THEN 'Enabled'                               ELSE 'Disabled'  END      AS JOB_ENABLED           ,l.name                                           AS JOB_OWNER          ,j.category_id                                    AS JOB_CATEGORY_ID       ,c.name                                           AS JOB_CATEGORY_NAME       ,[description]                                    AS JOB_DESCRIPTION          ,date_created                                     AS DATE_CREATED             ,date_modified                                    AS DATE_MODIFIED FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id INNER JOIN sys.syslogins l ON l.sid = j.owner_sid WHERE j.enabled =0 ORDER BY j.name 

--查看某个Owner的作业 SELECT j.job_id                                         AS JOB_ID                   ,j.name                                           AS JOB_NAME                 ,CASE WHEN [enabled] =1 THEN 'Enabled'                               ELSE 'Disabled'  END      AS JOB_ENABLED           ,l.name                                           AS JOB_OWNER          ,j.category_id                                    AS JOB_CATEGORY_ID       ,c.name                                           AS JOB_CATEGORY_NAME       ,[description]                                    AS JOB_DESCRIPTION          ,date_created                                     AS DATE_CREATED             ,date_modified                                    AS DATE_MODIFIED FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id INNER JOIN sys.syslogins l ON l.sid = j.owner_sid WHERE l.name='Domain/UserName' ORDER BY j.name 

SELECT j.job_id                                      AS JOB_ID                   ,j.name                                        AS JOB_NAME       ,s.step_id                                     AS Step_Id                 ,s.command                                     AS Command       ,CASE WHEN [enabled] =1 THEN 'Enabled'                               ELSE 'Disabled'  END   AS JOB_ENABLED              ,j.category_id                                 AS JOB_CATEGORY_ID       ,c.name                                        AS JOB_CATEGORY_NAME       ,[description]                                 AS JOB_DESCRIPTION          ,date_created                                  AS DATE_CREATED             ,date_modified                                 AS DATE_MODIFIED FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id ORDER BY j.name, s.step_id 

SELECT j.job_id                                      AS JOB_ID                   ,j.name                                        AS JOB_NAME       ,s.step_id                                     AS Step_Id                 ,s.command                                     AS Command       ,CASE WHEN [enabled] =1 THEN 'Enabled'                               ELSE 'Disabled'  END   AS JOB_ENABLED              ,j.category_id                                 AS JOB_CATEGORY_ID       ,c.name                                        AS JOB_CATEGORY_NAME       ,[description]                                 AS JOB_DESCRIPTION          ,date_created                                  AS DATE_CREATED             ,date_modified                                 AS DATE_MODIFIED FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id WHERE s.command LIKE '%procedure_name%' 

SELECT j.job_id                                      AS JOB_ID                   ,j.name                                        AS JOB_NAME                 ,CASE WHEN [enabled] =1 THEN 'Enabled'                               ELSE 'Disabled' END    AS JOB_ENABLED              ,j.category_id                                 AS JOB_CATEGORY_ID       ,c.name                                        AS JOB_CATEGORY_NAME       ,[description]                                 AS JOB_DESCRIPTION          ,date_created                                  AS DATE_CREATED             ,date_modified                                 AS DATE_MODIFIED FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id WHERE   job_id IN( SELECT job_id                   FROM    msdb.dbo.sysjobsteps                   WHERE   database_name = 'YourSQLDba' )  ORDER BY j.name 

SELECT j.job_id                                       AS JOB_ID                   ,j.name                                         AS JOB_NAME                 ,CASE WHEN [enabled] =1 THEN 'Enabled'                               ELSE 'Disabled' END     AS JOB_ENABLED              ,j.category_id                                  AS JOB_CATEGORY_ID       ,c.name                                         AS JOB_CATEGORY_NAME       ,description                                    AS JOB_DESCRIPTION          ,date_created                                   AS DATE_CREATED             ,date_modified                                  AS DATE_MODIFIED FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id  WHERE EXISTS     (SELECT 1 FROM msdb.dbo.sysjobsteps s WHERE              j.job_id = s.job_id AND s.subsystem='CmdExec') 

SELECT j.name                AS JOB_NAME               ,j.description         AS JOB_Description        ,j.date_created        AS Date_Created           ,j.date_modified       AS Date_Modified FROM  msdb.dbo.sysjobs j WHERE enabled = 1       AND EXISTS (         SELECT  1         FROM    Msdb.dbo.sysjobhistory h         WHERE   run_status = 0             AND j.job_id = h.job_id               AND run_date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT) ) 

SELECT j.name                AS job_name      , h.step_id             AS step_id      , h.step_name           AS job_step_name      , h.sql_severity        AS sql_severity      , h.[message]           AS error_message      , h.run_date            AS run_date      , h.run_time            AS run_time      , h.run_duration        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 j.job_name ='' ORDER BY j.name, h.run_date, h.run_time, h.step_id 

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 ORDER BY Job_Name, h.Step_Id 

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_Date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)  --今天执行的作业     AND h.run_status = 1    --执行成功 ORDER BY h.run_duration DESC 

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_Date = CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT)  --今天执行的作业     AND h.run_status = 1    --执行成功     AND h.run_duration > 100 --大于一分钟的作业 ORDER BY Job_Name, h.Step_Id 

SELECT job.job_id,        job.name  ,        sa.run_requested_date,        sa.start_execution_date FROM   msdb.dbo.sysjobs_view job        INNER JOIN msdb.dbo.sysjobactivity sa ON job.job_id = sa.job_id          -- get only the current session        INNER JOIN msdb.dbo.syssessions s ON s.session_id = sa.session_id        INNER JOIN ( SELECT MAX(agent_start_date) AS max_agent_start_date                     FROM   msdb.dbo.syssessions                   ) session_max ON s.agent_start_date = session_max.max_agent_start_date WHERE  sa.run_requested_date IS NOT NULL        AND sa.stop_execution_date IS NULL;