sql server 用脚本管理作业
转自：https://blog.csdn.net/yunye114105/article/details/6594826
1：业务场景：你想了解一下所有作业的Schedule信息，方便你作出调整或分析。例如作业的执行频率；例如你想查询那些作业是一小时执行一次的，那些是间隔几分钟执行一次的，使用下面脚本来查看吧。 2：业务场景：你想查看这个数据库实例有多少作业。 3：业务场景： 你想查看、统计哪些作业被禁止了。 --查看实例下被禁用的作业 4： 业务场景： 你想查看有那些账号在运行那些作业，例如某个同事离职了，但是他的那个NT账号在运行一些作业，你需要修改作业的Owner，否则当系统管理员禁用这个NT账号后，这些作业就会出错。 --查看某个Owner的作业 5：业务场景：你需要查看作业的具体步骤，例如作业执行的某个存储过程，或者有个同事想了解作业的运行情况，但是他不记得作业的名称，只知道这个作业执行了某个存储过程，那么就可以在下面SQL的基础上进行扩展。 --查询那个作业执行了某个存储过程（因为有时候，忘记了作业名称,但是知道那个作业调用了某个存储过程，此时开发人员找你帮忙找到对应的作业,就可以通过下面SQL查找） 6：业务场景：数据库迁移时，你想把属于这个数据库的作业也迁移走，但是那些作业是在这些数据库上面运行呢？ 可以通过下面SQL来查找。 ---查看属于某个数据库的作业(根据作业脚本执行的数据库判定) 7：业务场景：需要查看那些作业类型为"操作系统（CmdExec)"的作业。 8： 业务场景： 需要查看今天或某个时间段内出错的作业 --查询那些作业在今天出错（如果要查询历史出错作业，去掉查询时间条件即可） ---查看某个或所有作业的出错的历史记录信息 9： 业务场景： 查看作业的执行时间信息。--查询作业的运行时间，检查作业是否正常运行或存在问题。 10： 业务场景： 查询作业运行时间超过某个阀值的所有作业。例如作业执行时间超过一分钟的作业 查询今天执行时间大于一分钟的作业 11：业务场景，查看正在执行的作业。 12：业务场景，查看某个作业的详细信息 13.通过T-SQL修改作业中的计划时间 14.删除作业 15.禁用作业
16.删除备份作业历史记录
17.备份所有作业
18.作业是否执行成功简单判断方法
19.使用T-SQL新建作业、新建计划任务
20.修改作业的所有者
参考：sql server通过T-SQL执行作业
执行作业：EXEC sp_start_job @job_name = 'Nightly Backup'
获取作业信息： exec msdb.dbo.sp_help_job @job_name='db_backup'
【脚本管理作业场景】
1：job的任务计划，执行频率等
业务场景：你想了解一下所有作业的Schedule信息，方便你作出调整或分析。例如作业的执行频率；例如你想查询那些作业是一小时执行一次的，那些是间隔几分钟执行一次的，使用下面脚本来查看吧。
DECLARE @ManyTimes TABLE ( freq_relative_interval INT, times NVARCHAR(12) ) INSERT INTO @ManyTimes SELECT 1 ,N'第一个' UNION ALL SELECT 2 ,N'第二个' UNION ALL SELECT 4 ,N'第三个' UNION ALL SELECT 8 ,N'第四个' UNION ALL SELECT 16 ,N'最后一个' 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 (CASE WHEN s.freq_relative_interval = 0 THEN N'每月星期' + CONVERT(NVARCHAR(4),freq_interval) + N'执行' WHEN s.freq_relative_interval = 1 THEN ( CASE WHEN s.freq_interval =8 THEN N'每月第一天执行' ELSE N'每月' + (SELECT times FROM @ManyTimes WHERE freq_relative_interval=s.freq_relative_interval) + N'星期' + CONVERT(NVARCHAR(2),s.freq_interval -1) END ) WHEN s.freq_relative_interval = 2 THEN (CASE WHEN s.freq_interval =8 THEN N'每月第二天执行' ELSE N'每月'+ (SELECT times FROM @ManyTimes WHERE freq_relative_interval=s.freq_relative_interval) + N'星期' + CONVERT(NVARCHAR(2),s.freq_interval -1) END ) WHEN s.freq_relative_interval = 4 THEN (CASE WHEN s.freq_interval =8 THEN N'每月第三天执行' ELSE N'每月' + (SELECT times FROM @ManyTimes WHERE freq_relative_interval=s.freq_relative_interval) + N'星期' + CONVERT(NVARCHAR(2),s.freq_interval -1) END ) WHEN s.freq_relative_interval = 8 THEN (CASE WHEN s.freq_interval =8 THEN N'每月第四天执行' ELSE N'每月' + (SELECT times FROM @ManyTimes WHERE freq_relative_interval=s.freq_relative_interval) + N'星期' + CONVERT(NVARCHAR(2),s.freq_interval -1) END ) WHEN s.freq_relative_interval =16 THEN (CASE WHEN s.freq_interval =8 THEN N'每月最后一天执行' ELSE N'每月' + (SELECT times FROM @ManyTimes WHERE freq_relative_interval=s.freq_relative_interval) + N'星期' + CONVERT(NVARCHAR(2),s.freq_interval -1) END ) END ) 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
如下测试案例，非常的清晰明了，一目了然。
2：查看这个数据库实例有多少作业
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 ORDER BY j.name
--中文版
SELECT [jop].[job_id] AS '作业唯一标识符' ,[jop].[name] AS '作业名称' ,[dp].[name] AS '作业创建者' ,[cat].[name] AS '作业类别' ,[jop].[description] AS '作业描述' , CASE [jop].[enabled] WHEN 1 THEN '是' WHEN 0 THEN '否' END AS '是否启用' ,[jop].[date_created] AS '作业创建日期' ,[jop].[date_modified] AS '作业最后修改日期' ,[sv].[name] AS '作业运行服务器名称' ,[step].[step_id] AS '作业起始步骤' ,[step].[step_name] AS '步骤名称' , CASE WHEN [sch].[schedule_uid] IS NULL THEN '否' ELSE '是' END AS '是否分布式作业' ,[sch].[schedule_uid] AS '作业计划的唯一标识符' ,[sch].[name] AS '作业计划的用户定义名称' , CASE [jop].[delete_level] WHEN 0 THEN '不删除' WHEN 1 THEN '成功后删除' WHEN 2 THEN '失败后删除' WHEN 3 THEN '完成后删除' END AS '作业完成删除选项' FROM [msdb].[dbo].[sysjobs] AS [jop] LEFT JOIN [msdb].[sys].[servers] AS [sv] ON [jop].[originating_server_id] = [sv].[server_id] LEFT JOIN [msdb].[dbo].[syscategories] AS [cat] ON [jop].[category_id] = [cat].[category_id] LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [step] ON [jop].[job_id] = [step].[job_id] AND [jop].[start_step_id] = [step].[step_id] LEFT JOIN [msdb].[sys].[database_principals] AS [dp] ON [jop].[owner_sid] = [dp].[sid] LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [jsch] ON [jop].[job_id] = [jsch].[job_id] LEFT JOIN [msdb].[dbo].[sysschedules] AS [sch] ON [jsch].[schedule_id] = [sch].[schedule_id] ORDER BY [jop].[name]
在这个SQL上可以引申出跟多的SQL，例如查询某段时间内创建的作业等等
3：查看、统计哪些作业被禁止了
--查看实例下被禁用的作业 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
4：查看某个Owner的作业
业务场景： 你想查看有那些账号在运行那些作业，例如某个同事离职了，但是他的那个NT账号在运行一些作业，你需要修改作业的Owner，否则当系统管理员禁用这个NT账号后，这些作业就会出错。
--查看某个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
5：包含某个存储过程的作业
业务场景：你需要查看作业的具体步骤，例如作业执行的某个存储过程，或者有个同事想了解作业的运行情况，但是他不记得作业的名称，只知道这个作业执行了某个存储过程，那么就可以在下面SQL的基础上进行扩展。
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
--查询那个作业执行了某个存储过程（因为有时候，忘记了作业名称,但是知道那个作业调用了某个存储过程，此时开发人员找你帮忙找到对应的作业,就可以通过下面SQL查找）
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%'
6：查看属于某个数据库的作业
业务场景：数据库迁移时，你想把属于这个数据库的作业也迁移走，但是那些作业是在这些数据库上面运行呢？ 可以通过下面SQL来查找。
---查看属于某个数据库的作业(根据作业脚本执行的数据库判定)
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
7：查看作业类型为“操作系统（CmdExec) 的作业
业务场景：需要查看那些作业类型为“操作系统（CmdExec)"的作业。
--查看作业类型为“操作系统（CmdExec)"的作业
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')
8：查询在今天或某个时间段出错出错的作业
业务场景： 需要查看今天或某个时间段内出错的作业。
--查询那些作业在今天出错（如果要查询历史出错作业，去掉查询时间条件即可）
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
9：查看作业的运行时间
业务场景： 查看作业的执行时间信息。
--查询作业的运行时间，检查作业是否正常运行或存在问题。
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
10：查询今天执行时间大于一分钟的作业
业务场景： 查询作业运行时间超过某个阀值的所有作业。例如作业执行时间超过一分钟的作业
查询今天执行时间大于一分钟的作业
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
11：查看正在执行的作业
业务场景，查看正在执行的作业。
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;
12：作业详情
业务场景，查看某个作业的详细信息。
EXEC msdb.dbo.sp_help_job @Job_name = 'YourSQLDba_LogBackups'
13：通过T-SQL修改作业中的计划时间
1.获取作业的ID
USE [msdb] declare @job_id uniqueidentifier declare @schedule_id int SELECT @job_id = @job_id FROM dbo.sysjobs where name ='YourJobName'
2.获取计划ID
set @schedule_id = (select top 1 schedule_id from dbo.sysjobschedules where job_id = @job_id)
3.获取修改的SQL语句，并修改
4.最终
USE [msdb] declare @job_id uniqueidentifier declare @schedule_id int SELECT @job_id = job_id FROM dbo.sysjobs where name ='Test' set @schedule_id = (select top 1 schedule_id from dbo.sysjobschedules where job_id = @job_id) EXEC msdb.dbo.sp_attach_schedule @job_id,@schedule_id=@schedule_id EXEC msdb.dbo.sp_update_schedule @schedule_id=@schedule_id, @freq_type=4, @freq_interval=1, @freq_subday_type=1, @active_start_date=20141105, @active_start_time=103000
14：删除作业
14：删除作业
USE [msdb] go declare @job_name varchar(100) set @job_name = N'Db_Tank_Back_Restore' --MaintenancePlan为计划名 --删除在计划里面的日志 DELETE sysmaintplan_log FROM sysmaintplan_subplans AS subplans INNER JOIN sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id INNER JOIN sysmaintplan_log ON subplans.subplan_id = sysmaintplan_log.subplan_id WHERE (syjobs.name = @job_name) --删除代理的作业 DELETE sysjobschedules FROM sysjobs_view v INNER JOIN sysjobschedules o ON v.job_id=o.job_id WHERE v.name=@job_name --删除子计划 DELETE sysmaintplan_subplans FROM sysmaintplan_subplans AS subplans INNER JOIN sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id WHERE (syjobs.name = @job_name) --删除作业 DELETE FROM msdb.dbo.sysjobs_view WHERE NAME = @job_name GO
PRINT N'删除全部作业'
GO
DECLARE @sql NVARCHAR(MAX)
SET @sql = ''
SELECT @sql = @sql + 'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N''' + NAME + ''')'
+ CHAR(13) + CHAR(10) + ' EXEC msdb.dbo.sp_delete_job @job_name=N''' + NAME + ''' , @delete_unused_schedule=1'
+ CHAR(13) + CHAR(10)
FROM msdb.dbo.sysjobs
15：禁用作业、启用作业
USE msdb ; GO EXEC dbo.sp_update_job @job_name = N'作业名称',--这里填上作业的名称 @new_name = N'作业名称',--这里填上作业的名称 @description = N'',--这个是作业的说明可以不填 @enabled = 1 ;--这个值表示启用还是禁用，1为启用作业 ，0为禁用作业 GO
16：删除作业的历史记录
EXEC msdb.dbo.sp_delete_backuphistory @Day[ @oldest_date = ] 'oldest\_date'备份和还原历史记录表中保留的最早日期。 oldest_date是datetime，无默认值。
sp_purge_jobhistory
{ [ @job_name = ] 'job_name' |
| [ @job_id = ] job_id }
[ , [ @oldest_date = ] oldest_date ]
-- @job_name和@job_id输入一个即可，@oldest_date 表示删除这个时间之前的所有作业历史记录
参考：CSDN
17：备份所有作业
USE [master] GO /****** Object: StoredProcedure [dbo].[DumpJobsql] Script Date: 02/07/2014 11:38:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO alter PROCEDURE [dbo].[usp_DumpJobsql] AS BEGIN -- Author: KK -- Create date: 2016-09-27 -- Description: 备份JOB，目前不支持邮件 -- 生成数据一份保留在master的zzz_temp_JOB_bcp表中，另外会在目标位置生成一个 job_日期.sql -- http://www.cnblogs.com/double-K/ SET NOCOUNT ON DECLARE @SV nvarchar(4) DECLARE @i_enabled TINYINT DECLARE @sql VARCHAR(max) DECLARE @i_job_name VARCHAR(1000) DECLARE @i_notify_level_eventlog INT DECLARE @i_notify_level_email INT DECLARE @i_notify_level_netsend INT DECLARE @i_notify_level_page INT DECLARE @i_delete_level INT DECLARE @i_description VARCHAR(1000) DECLARE @i_category_name VARCHAR(1000) DECLARE @i_owner_login_name VARCHAR(1000) DECLARE @i_category_class INT DECLARE @i_start_step_id INT DECLARE @i_step_name VARCHAR(1000) DECLARE @i_step_id INT DECLARE @i_cmdexec_success_code INT DECLARE @i_on_success_action INT DECLARE @i_on_success_step_id INT DECLARE @i_on_fail_action INT DECLARE @i_on_fail_step_id INT DECLARE @i_retry_attempts BIGINT DECLARE @i_retry_interval INT DECLARE @i_os_run_priority INT DECLARE @i_subsystem VARCHAR(1000) DECLARE @i_command VARCHAR(8000) DECLARE @i_database_name VARCHAR(100) DECLARE @i_flags INT DECLARE @i_class VARCHAR(10) ,@i_type VARCHAR(10) DECLARE @c_jobid UNIQUEIDENTIFIER ,@c_categoryid INT DECLARE @loop_stepid INT DECLARE @m_stepid INT DECLARE @loop_scheduleid INT DECLARE @m_scheduleid INT DECLARE @i_schedule_enabled TINYINT DECLARE @i_freq_type INT DECLARE @i_schedule_name VARCHAR(1000) DECLARE @i_freq_interval INT DECLARE @i_freq_subday_type INT DECLARE @i_freq_subday_interval INT DECLARE @i_freq_relative_interval INT DECLARE @i_freq_recurrence_factor INT DECLARE @i_active_start_date BIGINT DECLARE @i_active_end_date BIGINT DECLARE @i_active_start_time BIGINT DECLARE @i_active_end_time BIGINT DECLARE @i_schedule_uid VARCHAR(1000) SET @i_class = 'JOB' SET @i_type = 'LOCAL' if exists (select 1 from sys.objects where name = 'zzz_temp_JOB_bcp') begin delete from master..zzz_temp_JOB_bcp end else begin create table zzz_temp_JOB_bcp(name nvarchar(100),text nvarchar(max),sv nvarchar(4),Bak_date nvarchar(10)) end DECLARE job CURSOR FOR SELECT a.job_id ,a.category_id,'服务器XX' as SV FROM msdb.dbo.sysjobs a , msdb.dbo.syscategories c WHERE a.category_id = c.category_id AND c.name NOT LIKE '%Database Maintenance%' AND c.name NOT LIKE '%REPL%' AND c.name <> 'Log Shipping' AND a.name <> 'syspolicy_purge_history' ----如果需要可多服务器备份 --union all --select a.job_id ,a.category_id,'服务器XXX' --from --opendatasource('SQLOLEDB','Data Source=XX.XX.XX.XX;User ID=XX;Password=XX').msdb.dbo.sysjobs a, --opendatasource('SQLOLEDB','Data Source=XX.XX.XX.XX;User ID=XX;Password=XX').msdb.dbo.syscategories c --WHERE a.category_id = c.category_id -- AND c.name NOT LIKE '%Database Maintenance%' -- AND c.name NOT LIKE '%REPL%' -- AND c.name <> 'Log Shipping' -- AND a.name <> 'syspolicy_purge_history' OPEN job FETCH job INTO @c_jobid ,@c_categoryid,@SV WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = '' SELECT @i_job_name = a.name , @i_enabled = [enabled] , @i_notify_level_eventlog = notify_level_eventlog , @i_notify_level_email = notify_level_email , @i_notify_level_netsend = notify_level_netsend , @i_notify_level_page = notify_level_page , @i_delete_level = delete_level , @i_description = [description] , @i_category_name = c.name , @i_owner_login_name = ISNULL(SUSER_SNAME(a.owner_sid), N'''') , @i_category_class = category_class FROM msdb.dbo.sysjobs a ,msdb.dbo.syscategories c WHERE a.category_id=c.category_id AND a.job_id=@c_jobid AND a.category_id = @c_categoryid SET @sql=@sql+CHAR(13)+CHAR(10) + 'USE [msdb]' SET @sql=@sql+CHAR(13)+CHAR(10) + 'GO' SET @sql=@sql+CHAR(13)+CHAR(10) + '/****** Object: Job ['+ @i_job_name +'] Script Date: '+CONVERT(VARCHAR,GETDATE(),22)+' ******/' SET @sql=@sql+CHAR(13)+CHAR(10) + 'BEGIN TRANSACTION' SET @sql=@sql+CHAR(13)+CHAR(10) + 'DECLARE @ReturnCode INT' SET @sql=@sql+CHAR(13)+CHAR(10) + 'SELECT @ReturnCode = 0' SET @sql=@sql+CHAR(13)+CHAR(10) + '/****** Object: JobCategory ['+ @i_category_name +'] Script Date: 08/20/2016 12:35:16 ******/' SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'''+ @i_category_name +''' AND category_class='+ CAST(@i_category_class AS VARCHAR) +' )' SET @sql=@sql+CHAR(13)+CHAR(10) + 'BEGIN' SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'''+ @i_class +''', @type=N'''+ @i_type +''', @name=N'''+ @i_category_name +'''' SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' SET @sql=@sql+CHAR(13)+CHAR(10) + '' SET @sql=@sql+CHAR(13)+CHAR(10) + 'END' SET @sql=@sql+CHAR(13)+CHAR(10) + '' SET @sql=@sql+CHAR(13)+CHAR(10) + 'DECLARE @jobId BINARY(16)' SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'''+ @i_job_name +''',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @enabled='+ CAST(@i_enabled AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @notify_level_eventlog='+ CAST(@i_notify_level_eventlog AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @notify_level_email='+ CAST(@i_notify_level_email AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @notify_level_netsend='+ CAST(@i_notify_level_netsend AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @notify_level_page='+ CAST(@i_notify_level_page AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @delete_level='+ CAST(@i_delete_level AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @description=N'''+ @i_description +''',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @category_name=N'''+ @i_category_name +''',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @owner_login_name=N'''+ @i_owner_login_name +''', @job_id = @jobId OUTPUT' SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' IF EXISTS ( SELECT TOP 1 1 FROM msdb.dbo.sysjobsteps WHERE job_id = @c_jobid ) BEGIN SELECT @loop_stepid = MIN(step_id) ,@m_stepid = MAX(step_id) FROM msdb.dbo.sysjobsteps WHERE job_id = @c_jobid WHILE (@loop_stepid < = @m_stepid) BEGIN SELECT @i_start_step_id = start_step_id, @i_step_name = step_name , @i_step_id = step_id, @i_cmdexec_success_code = cmdexec_success_code , @i_on_success_action = on_success_action , @i_on_success_step_id = on_success_step_id , @i_on_fail_action = on_fail_action , @i_on_fail_step_id = on_fail_step_id , @i_retry_attempts = retry_attempts , @i_retry_interval = retry_interval , @i_os_run_priority = os_run_priority , @i_subsystem = subsystem , @i_command = command , @i_database_name = database_name , @i_flags = flags FROM msdb.dbo.sysjobs a ,msdb.dbo.sysjobsteps b WHERE a.job_id = b.job_id AND step_id = @loop_stepid AND a.job_id = @c_jobid SET @sql=@sql+CHAR(13)+CHAR(10) + '/****** Object: Step ['+ @i_step_name +'] Script Date: '+CONVERT(VARCHAR,GETDATE(),22)+' ******/' SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'''+ @i_step_name +''',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @step_id='+ CAST(@i_step_id AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @cmdexec_success_code='+ CAST(@i_cmdexec_success_code AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @on_success_action='+ CAST(@i_on_success_action AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @on_success_step_id='+ CAST(@i_on_success_step_id AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @on_fail_action='+ CAST(@i_on_fail_action AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @on_fail_step_id='+ CAST(@i_on_fail_step_id AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @retry_attempts='+ CAST(@i_retry_attempts AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @retry_interval='+ CAST(@i_retry_interval AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @os_run_priority='+ CAST(@i_os_run_priority AS VARCHAR) +', @subsystem=N'''+ @i_subsystem +''',' SET @sql=@sql+CHAR(13)+CHAR(10) + ISNULL(' @command=N''' + REPLACE(@i_command ,'''' ,'''''') + ''',' ,'') SET @sql=@sql+CHAR(13)+CHAR(10) + ISNULL(' @database_name=N'''+ @i_database_name +''',' ,'') SET @sql=@sql+CHAR(13)+CHAR(10) + ' @flags='+ CAST(@i_flags AS VARCHAR) SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' SET @loop_stepid = ( SELECT TOP 1 step_id FROM msdb.dbo.sysjobsteps WHERE job_id = @c_jobid AND step_id > @loop_stepid ORDER BY step_id ) END END SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = '+ CAST(@i_start_step_id AS VARCHAR) SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' IF EXISTS ( SELECT TOP 1 1 FROM msdb.dbo.sysschedules c ,msdb.dbo.sysjobschedules d WHERE c.schedule_id = d.schedule_id AND job_id = @c_jobid ) BEGIN SELECT @loop_scheduleid= MIN(c.schedule_id) ,@m_scheduleid = MAX(c.schedule_id) FROM msdb.dbo.sysschedules c ,msdb.dbo.sysjobschedules d WHERE c.schedule_id = d.schedule_id AND job_id = @c_jobid WHILE ( @loop_scheduleid <= @m_scheduleid ) BEGIN SELECT @i_schedule_enabled = [enabled] , @i_freq_type = freq_type , @i_schedule_name = name, @i_freq_interval = freq_interval , @i_freq_subday_type = freq_subday_type , @i_freq_subday_interval = freq_subday_interval , @i_freq_relative_interval = freq_relative_interval , @i_freq_recurrence_factor = freq_recurrence_factor , @i_active_start_date = active_start_date , @i_active_end_date = active_end_date , @i_active_start_time = active_start_time , @i_active_end_time = active_end_time , @i_schedule_uid = schedule_uid FROM msdb.dbo.sysschedules c LEFT JOIN msdb.dbo.sysjobschedules d ON c.schedule_id = d.schedule_id WHERE d.job_id = @c_jobid AND c.schedule_id = @loop_scheduleid SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'''+ @i_schedule_name +''',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @enabled='+ CAST(@i_schedule_enabled AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @freq_type='+ CAST(@i_freq_type AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @freq_interval='+ CAST(@i_freq_interval AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @freq_subday_type='+ CAST(@i_freq_subday_type AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @freq_subday_interval='+ CAST(@i_freq_subday_interval AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @freq_relative_interval='+ CAST(@i_freq_relative_interval AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @freq_recurrence_factor='+ CAST(@i_freq_recurrence_factor AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @active_start_date='+ CAST(@i_active_start_date AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @active_end_date='+ CAST(@i_active_end_date AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @active_start_time='+ CAST(@i_active_start_time AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @active_end_time='+ CAST(@i_active_end_time AS VARCHAR) +',' SET @sql=@sql+CHAR(13)+CHAR(10) + ' @schedule_uid=N'''+ @i_schedule_uid +'''' SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' SET @loop_scheduleid = ( SELECT TOP 1 c.schedule_id FROM msdb.dbo.sysschedules c ,msdb.dbo.sysjobschedules d WHERE c.schedule_id = d.schedule_id AND job_id = @c_jobid AND c.schedule_id > @loop_scheduleid ) END END SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)''' SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' SET @sql=@sql+CHAR(13)+CHAR(10) + 'COMMIT TRANSACTION' SET @sql=@sql+CHAR(13)+CHAR(10) + 'GOTO EndSave' SET @sql=@sql+CHAR(13)+CHAR(10) + 'QuitWithRollback:' SET @sql=@sql+CHAR(13)+CHAR(10) + ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION' SET @sql=@sql+CHAR(13)+CHAR(10) + 'EndSave:' SET @sql=@sql+CHAR(13)+CHAR(10) + '' SET @sql=@sql+CHAR(13)+CHAR(10) + 'GO' PRINT @sql insert into master..zzz_temp_JOB_bcp SELECT @i_job_name,@sql,@SV,CONVERT(nvarchar(10),getdate(),112) FETCH NEXT FROM job INTO @c_jobid ,@c_categoryid ,@SV END CLOSE job DEALLOCATE job declare @a nvarchar(17),@c nvarchar(1000),@name nvarchar(100),@d nvarchar(100) set @a = CONVERT (nvarchar(17),GETDATE(),112) set @name = 'D:\DBA_TOOLS\job_'+@a+'.sql' set @d = 'del ' + @name set @c = 'bcp "select text from master..zzz_temp_JOB_bcp where bak_date = CONVERT(nvarchar(10),getdate(),112)" queryout "'+ @name +'" -c -S"服务名称" -U"sa" -P"sa123456" ' print @d print @c exec sp_configure 'show advanced options',1 reconfigure with override exec sp_configure 'xp_cmdshell',1 reconfigure with override EXEC master..xp_cmdshell @d EXEC master..xp_cmdshell @c exec sp_configure 'xp_cmdshell',0 reconfigure with override exec sp_configure 'show advanced options',0 reconfigure with override end GO exec [usp_DumpJobsql]
18：作业是否执行成功简单判断方法
查看某个作业的执行情况
set @fullbakjob=(SELECT case when count(HIST.run_status)=sum(HIST.run_status) then 1 else 0 end stat FROM msdb.dbo.sysjobs JOB INNER JOIN msdb.dbo.sysjobhistory HIST ON HIST.job_id = JOB.job_id WHERE JOB.name in ('Db_Tank_Back_Restore') AND HIST.run_date= convert(char(10),GetDate(),112) AND HIST.run_time<'60000');
查看所有作业的执行情况
select JOB.name,run_date,(SELECT case when count(HIST.run_status)=sum(HIST.run_status) then 1 else 0 end stat ) as stat FROM msdb.dbo.sysjobs JOB INNER JOIN msdb.dbo.sysjobhistory HIST ON HIST.job_id = JOB.job_id where run_date>='20200718' group by job.name,run_date
19：使用T-SQL新建作业
--使用 Transact-SQL 创建作业 --1. 使用sp_add_job若要创建一个名为作业Daily SampleDB Backup。 -- Adds a new job executed by the SQLServerAgent service -- called 'Daily SampleDB Backup' USE msdb ; GO EXEC dbo.sp_add_job @job_name = N'Daily SampleDB Backup' ; GO --2. 调用sp_add_jobstep若要创建的备份创建的作业步骤SampleDB数据库。 -- Adds a step (operation) to the job EXEC sp_add_jobstep @job_name = N'Daily SampleDB Backup', @step_name = N'Backup database', @subsystem = N'TSQL', @command = N'BACKUP DATABASE SampleDB TO DISK = \ N''/var/opt/mssql/data/SampleDB.bak'' WITH NOFORMAT, NOINIT, \ NAME = ''SampleDB-full'', SKIP, NOREWIND, NOUNLOAD, STATS = 10', @retry_attempts = 5, @retry_interval = 5 ; GO --3. 然后创建与作业的每日计划sp_add_schedule。 -- Creates a schedule called 'Daily' EXEC dbo.sp_add_schedule @schedule_name = N'Daily SampleDB', @freq_type = 4, @freq_interval = 1, @active_start_time = 233000 ; USE msdb ; GO --4. 将作业计划附加到作业sp_attach_schedule。 -- Sets the 'Daily' schedule to the 'Daily SampleDB Backup' Job EXEC sp_attach_schedule @job_name = N'Daily SampleDB Backup', @schedule_name = N'Daily SampleDB'; GO --5. 使用sp_add_jobserver若要将作业分配到目标服务器。 在此示例中，目标是本地服务器。 EXEC dbo.sp_add_jobserver @job_name = N'Daily SampleDB Backup', @server_name = N'(LOCAL)'; GO --6. 启动的作业sp_start_job。 EXEC dbo.sp_start_job N' Daily SampleDB Backup' ; GO
20：修改作业的所有者
1、首先介绍几个所有者相关的表sys.syslogins每个登录帐户在表中对应一行； 直接查询这个表： select * from master.dbo.syslogins select * from sys.database_principals 可以查看都有哪些登录用户的被限制权限了 2、查询所有作业的表：sysjobs（可以去官网了解文档内容） select * from msdb.dbo.sysjobs 其中的sid和所有者表里的owner_sid是一致的 所以下面查询被废弃的所有者号下的所有作业 复制代码 --可以用这个先查一遍。 select a.*,b.name from ( select * from msdb.dbo.sysjobs where owner_sid<>0x01 ) a left join master.sys.syslogins b on a.owner_sid=b.sid where b.name like '%这里换成将要删除的帐号%' 下面是自动化更新所有涉及到的作业的所有者 declare @job_id varchar(300) select a.job_id into #ttt from ( select name,[enabled],owner_sid,job_id from msdb.dbo.sysjobs where owner_sid<>0x01 ) a left join master.sys.syslogins b on a.owner_sid=b.sid where b.name is null or b.name like '%这里换成将要删除的帐号%' while (select count(1) from #ttt)>0 begin select top 1 @job_id=job_id from #ttt order by job_id asc declare @sql varchar(500) select @sql = 'EXEC msdb.dbo.sp_update_job @job_id=N'''+@job_id+''', @owner_login_name=N''sa''' print (@sql) exec (@sql) delete from #ttt where job_id=@job_id end truncate table #ttt drop table #ttt
【作业介绍】
SQL SERVER的作业是一系列由SQL SERVER代理按顺序执行的指定操作。作业可以执行一系列活动，包括运行Transact-SQL脚本、命令行应用程序、Microsoft ActiveX脚本、Integration Services 包、Analysis Services 命令和查询或复制任务。
作业可以运行重复任务或那些可计划的任务，它们可以通过生成警报来自动通知用户作业状态，从而极大地简化了 SQL Server 管理[参见MSDN]。
创建作业、删除作业、查看作业历史记录....等所有操作都可以通过SSMS管理工具GUI界面操作，有时候也确实挺方便的。
但是当一个实例有多个作业或多个数据库实例时，通过图形化的界面去管理、维护作业也是个头痛的问题，对于SQL脚本与GUI界面管理维护作业熟优熟劣这个问题，只能说要看场合。下面主要介绍通过SQL脚本来管理、维护作业。
作业分类（增删查改作业类别）
创建作业时，往往需要指定作业类别，如果不指定新建作业类别，就会默认为“[未分类(本地)]”，如下截图所示：
当然，你可以查看、添加、删除、修改作业分类。请看下面操作。
1：查看作业分类
Code Snippet
--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_help_category
2：添加作业分类
如下所示，添加一个叫"DBA_MONITORING"的作业分类
Code Snippet
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
有兴趣的可以研究一下存储过程msdb.dbo.sp_add_category
sp_add_category3：删除作业分类
如下所示，删除一个叫"DBA_MONITORING" 的作业分类
Code Snippet
EXEC msdb.dbo.sp_delete_category @name = N'DBA_MONITORING', @class = N'JOB' ; GO
有兴趣的可以研究一下存储过程msdb.dbo.sp_delete_category
sp_delete_category4：修改作业类别
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进行操作，只是通过存储过程封装了而已，增加了验证等操作，确保数据完整性。
新建作业
创建作业的步骤一般如下所示：
- 执行 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 将更为有效。
下面看用脚本新建一个作业用来每天执行exec sp_cycle_errorlog ，实现错误日志循环， 从下面的脚本量来看，用脚本新建一个作业确实工作量很大，而且容易出错，GUI图形界面创建作业要方便得多，但是如果迁移数据库时，用脚本来新建作业是相当方便的。比GUI图形界面新建一个作业快捷方便多了。
Code Snippet
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
作业系统表
与作业有关的系统表、视图大致有下面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 = 0 ; --0 禁用作业、 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 编写作业脚本
-
在对象资源管理器中，连接到 Microsoft SQL Server 数据库引擎实例，再展开该实例。
-
展开“SQL Server 代理”，再展开“作业”，然后右键单击要编写脚本的作业。
-
从快捷菜单中，选择“编写作业脚本为”，再选择“CREATE 到”或“DROP 到”，并单击下列内容之一：
新查询编辑器窗口，将打开一个新的查询编辑器窗口，并为其编写 Transact-SQL 脚本。
文件，将 Transact-SQL 脚本保存到文件。
剪贴板，将 Transact-SQL 脚本保存到剪贴板
【常用管理作业SQL】
1:查看属于某个数据库的所有作业。
Code Snippet
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：查询那些作业在今天出错（如果要查询历史出错作业，去掉查询时间条件即可）
Code Snippet
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：查看出错详细信息
Code Snippet
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信息）
Code Snippet
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：查询每个作业的执行时间、按执行时间降序
Code Snippet
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
