Tenormis's Blog

Appearance

SQL Server 作业管理 ​

1. 执行一次 ​

SQL
USE msdb;
GO

DECLARE @jobName sysname, @dbname sysname, @sql NVARCHAR(MAX);
SET @jobName = N'$JobName$';
SET @dbname = N'$DBName$';
SET @sql = N'$Command$';

DECLARE @job_id UNIQUEIDENTIFIER;
EXEC msdb.dbo.sp_add_job @job_name = @jobName, @job_id = @job_id OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id, @step_name = @jobName, @subsystem = N'TSQL', @command = @sql, @database_name = @dbname;

-- 频率：仅一次
-- 日期：在 $20200101$
-- 时间：在 $120000$
EXEC msdb.dbo.sp_add_jobschedule @job_id = @job_id, @name = @jobName, @enabled = 1, @freq_type = 1, @active_start_date = $20200101$, @active_start_time = $120000$;

DECLARE @servername SYSNAME;
SET @servername = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'ServerName'));
EXEC msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name = @servername;

GO

2. 每 X 天执行一次 ​

SQL
USE msdb;
GO

DECLARE @jobName sysname, @dbname sysname, @sql NVARCHAR(MAX);
SET @jobName = N'$JobName$';
SET @dbname = N'$DBName$';
SET @sql = N'$Command$';

DECLARE @job_id UNIQUEIDENTIFIER;
EXEC msdb.dbo.sp_add_job @job_name = @jobName, @job_id = @job_id OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id, @step_name = @jobName, @subsystem = N'TSQL', @command = @sql, @database_name = @dbname;

-- 频率：每 $1$ 日
-- 日期：从 $20200101$ 到 $99991231$
-- 时间：在 $120000$
EXEC msdb.dbo.sp_add_jobschedule @job_id = @job_id
                               , @name = @jobName
                               , @enabled = 1
                               , @freq_type = 4
                               , @freq_interval = $1$
                               , @freq_subday_type = 1
                               , @active_start_date = $20200101$
                               , @active_end_date = $99991231$
                               , @active_start_time = $120000$;

DECLARE @servername SYSNAME;
SET @servername = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'ServerName'));
EXEC msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name = @servername;

GO

3. 每 X 天重复执行 ​

SQL
USE msdb;
GO

DECLARE @jobName sysname, @dbname sysname, @sql NVARCHAR(MAX);
SET @jobName = N'$JobName$';
SET @dbname = N'$DBName$';
SET @sql = N'$Command$';

DECLARE @job_id UNIQUEIDENTIFIER;
EXEC msdb.dbo.sp_add_job @job_name = @jobName, @job_id = @job_id OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id, @step_name = @jobName, @subsystem = N'TSQL', @command = @sql, @database_name = @dbname;

-- 频率：每 $1$ 日
-- 日期：从 $20200101$ 到 $99991231$
-- 时间：在 $120000$ 到 $235959$ 之间，每 $10$ [$4$:2-秒|4-分钟|8-小时]
EXEC msdb.dbo.sp_add_jobschedule @job_id = @job_id
                               , @name = @jobName
                               , @enabled = 1
                               , @freq_type = 4
                               , @freq_interval = $1$
                               , @freq_subday_type = $4$
                               , @freq_subday_interval = $10$
                               , @active_start_date = $20200101$
                               , @active_end_date = $99991231$
                               , @active_start_time = $120000$
                               , @active_end_time = $235959$;

DECLARE @servername SYSNAME;
SET @servername = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'ServerName'));
EXEC msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name = @servername;

GO

4. 每 X 周的 Y 日执行一次 ​

SQL
USE msdb;
GO

DECLARE @jobName sysname, @dbname sysname, @sql NVARCHAR(MAX);
SET @jobName = N'$JobName$';
SET @dbname = N'$DBName$';
SET @sql = N'$Command$';

DECLARE @job_id UNIQUEIDENTIFIER;
EXEC msdb.dbo.sp_add_job @job_name = @jobName, @job_id = @job_id OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id, @step_name = @jobName, @subsystem = N'TSQL', @command = @sql, @database_name = @dbname;

-- 频率：每 $1$ 周的{$2$:1-星期日|2-星期一|4-星期二|8-星期三|16-星期四|32-星期五|64-星期六}
-- 日期：从 $20200101$ 到 $99991231$
-- 时间：在 $120000$
EXEC msdb.dbo.sp_add_jobschedule @job_id = @job_id
                               , @name = @jobName
                               , @enabled = 1
                               , @freq_type = 8
                               , @freq_interval = $2$
                               , @freq_subday_type = 1
                               , @freq_recurrence_factor = $1$
                               , @active_start_date = $20200101$
                               , @active_end_date = $99991231$
                               , @active_start_time = $120000$;

DECLARE @servername SYSNAME;
SET @servername = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'ServerName'));
EXEC msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name = @servername;

GO

5. 每 X 周的 Y 日重复执行 ​

SQL
USE msdb;
GO

DECLARE @jobName sysname, @dbname sysname, @sql NVARCHAR(MAX);
SET @jobName = N'$JobName$';
SET @dbname = N'$DBName$';
SET @sql = N'$Command$';

DECLARE @job_id UNIQUEIDENTIFIER;
EXEC msdb.dbo.sp_add_job @job_name = @jobName, @job_id = @job_id OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id, @step_name = @jobName, @subsystem = N'TSQL', @command = @sql, @database_name = @dbname;

-- 频率：每 $1$ 周的{$2$:1-星期日|2-星期一|4-星期二|8-星期三|16-星期四|32-星期五|64-星期六}
-- 日期：从 $20200101$ 到 $99991231$
-- 时间：在 $120000$ 到 $235959$ 之间，每 $10$ [$4$:2-秒|4-分钟|8-小时]
EXEC msdb.dbo.sp_add_jobschedule @job_id = @job_id
                               , @name = @jobName
                               , @enabled = 1
                               , @freq_type = 8
                               , @freq_interval = $2$
                               , @freq_subday_type = $4$
                               , @freq_subday_interval = $10$
                               , @freq_recurrence_factor = $1$
                               , @active_start_date = $20200101$
                               , @active_end_date = $99991231$
                               , @active_start_time = $120000$
                               , @active_end_time = $235959$;

DECLARE @servername SYSNAME;
SET @servername = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'ServerName'));
EXEC msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name = @servername;

GO

6. 每 X 月的 Y 日执行一次 ​

SQL
USE msdb;
GO

DECLARE @jobName sysname, @dbname sysname, @sql NVARCHAR(MAX);
SET @jobName = N'$JobName$';
SET @dbname = N'$DBName$';
SET @sql = N'$Command$';

DECLARE @job_id UNIQUEIDENTIFIER;
EXEC msdb.dbo.sp_add_job @job_name = @jobName, @job_id = @job_id OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id, @step_name = @jobName, @subsystem = N'TSQL', @command = @sql, @database_name = @dbname;

-- 频率：每 $1$ 月的 $30$ 日
-- 日期：从 $20200101$ 到 $99991231$
-- 时间：在 $120000$
EXEC msdb.dbo.sp_add_jobschedule @job_id = @job_id
                               , @name = @jobName
                               , @enabled = 1
                               , @freq_type = 16
                               , @freq_interval = $30$
                               , @freq_subday_type = 1
                               , @freq_recurrence_factor = $1$
                               , @active_start_date = $20200101$
                               , @active_end_date = $99991231$
                               , @active_start_time = $120000$;

DECLARE @servername SYSNAME;
SET @servername = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'ServerName'));
EXEC msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name = @servername;

GO

7. 每 X 月的 Y 日重复执行 ​

SQL
USE msdb;
GO

DECLARE @jobName sysname, @dbname sysname, @sql NVARCHAR(MAX);
SET @jobName = N'$JobName$';
SET @dbname = N'$DBName$';
SET @sql = N'$Command$';

DECLARE @job_id UNIQUEIDENTIFIER;
EXEC msdb.dbo.sp_add_job @job_name = @jobName, @job_id = @job_id OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id, @step_name = @jobName, @subsystem = N'TSQL', @command = @sql, @database_name = @dbname;

-- 频率：每 $1$ 月的 $30$ 日
-- 日期：从 $20200101$ 到 $99991231$
-- 时间：在 $120000$ 到 $235959$ 之间，每 $10$ [$4$:2-秒|4-分钟|8-小时]
EXEC msdb.dbo.sp_add_jobschedule @job_id = @job_id
                               , @name = @jobName
                               , @enabled = 1
                               , @freq_type = 16
                               , @freq_interval = $30$
                               , @freq_subday_type = $4$
                               , @freq_subday_interval = $10$
                               , @freq_recurrence_factor = $1$
                               , @active_start_date = $20200101$
                               , @active_end_date = $99991231$
                               , @active_start_time = $120000$
                               , @active_end_time = $235959$;

DECLARE @servername SYSNAME;
SET @servername = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'ServerName'));
EXEC msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name = @servername;

GO

8. 每 X 月第 Y 次星期 Z 执行一次 ​

SQL
USE msdb;
GO

DECLARE @jobName sysname, @dbname sysname, @sql NVARCHAR(MAX);
SET @jobName = N'$JobName$';
SET @dbname = N'$DBName$';
SET @sql = N'$Command$';

DECLARE @job_id UNIQUEIDENTIFIER;
EXEC msdb.dbo.sp_add_job @job_name = @jobName, @job_id = @job_id OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id, @step_name = @jobName, @subsystem = N'TSQL', @command = @sql, @database_name = @dbname;

-- 频率：每 $1$ 月[$16$:1-第一次|2-第二次|4-第三次|8-第四次|16-最后一次]
--       的[$2$:1-星期日|2-星期一|3-星期二|4-星期三|5-星期四|6-星期五|7-星期六|8-天|9-工作日|10-休息日]
-- 日期：从 $20200101$ 到 $99991231$
-- 时间：在 $120000$
EXEC msdb.dbo.sp_add_jobschedule @job_id = @job_id
                               , @name = @jobName
                               , @enabled = 1
                               , @freq_type = 32
                               , @freq_interval = $2$
                               , @freq_subday_type = 1
                               , @freq_relative_interval = $16$
                               , @freq_recurrence_factor = $1$
                               , @active_start_date = $20200101$
                               , @active_end_date = $99991231$
                               , @active_start_time = $120000$;

DECLARE @servername SYSNAME;
SET @servername = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'ServerName'));
EXEC msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name = @servername;

GO

9. 每 X 月第 Y 次星期 Z 重复执行 ​

SQL
USE msdb;
GO

DECLARE @jobName sysname, @dbname sysname, @sql NVARCHAR(MAX);
SET @jobName = N'$JobName$';
SET @dbname = N'$DBName$';
SET @sql = N'$Command$';

DECLARE @job_id UNIQUEIDENTIFIER;
EXEC msdb.dbo.sp_add_job @job_name = @jobName, @job_id = @job_id OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id, @step_name = @jobName, @subsystem = N'TSQL', @command = @sql, @database_name = @dbname;

-- 频率：每 $1$ 月[$16$:1-第一次|2-第二次|4-第三次|8-第四次|16-最后一次]
--       的[$2$:1-星期日|2-星期一|3-星期二|4-星期三|5-星期四|6-星期五|7-星期六|8-天|9-工作日|10-休息日]
-- 日期：从 $20200101$ 到 $99991231$
-- 时间：在 $120000$ 到 $235959$ 之间，每 $10$ [$4$:2-秒|4-分钟|8-小时]
EXEC msdb.dbo.sp_add_jobschedule @job_id = @job_id
                               , @name = @jobName
                               , @enabled = 1
                               , @freq_type = 32
                               , @freq_interval = $2$
                               , @freq_subday_type = $4$
                               , @freq_subday_interval = $10$
                               , @freq_relative_interval = $16$
                               , @freq_recurrence_factor = $1$
                               , @active_start_date = $20200101$
                               , @active_end_date = $99991231$
                               , @active_start_time = $120000$
                               , @active_end_time = $235959$;

DECLARE @servername SYSNAME;
SET @servername = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'ServerName'));
EXEC msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name = @servername;

GO

10. 空闲时运行 ​

SQL
USE msdb;
GO

DECLARE @jobName sysname, @dbname sysname, @sql NVARCHAR(MAX);
SET @jobName = N'$JobName$';
SET @dbname = N'$DBName$';
SET @sql = N'$Command$';

DECLARE @job_id UNIQUEIDENTIFIER;
EXEC msdb.dbo.sp_add_job @job_name = @jobName, @job_id = @job_id OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id, @step_name = @jobName, @subsystem = N'TSQL', @command = @sql, @database_name = @dbname;

-- 在计算机处于空闲状态时运行
EXEC msdb.dbo.sp_add_jobschedule @job_id = @job_id, @name = @jobName, @enabled = 1, @freq_type = 128;

DECLARE @servername SYSNAME;
SET @servername = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'ServerName'));
EXEC msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name = @servername;

GO

11. 代理服务启动时运行 ​

SQL
USE msdb;
GO

DECLARE @jobName sysname, @dbname sysname, @sql NVARCHAR(MAX);
SET @jobName = N'$JobName$';
SET @dbname = N'$DBName$';
SET @sql = N'$Command$';

DECLARE @job_id UNIQUEIDENTIFIER;
EXEC msdb.dbo.sp_add_job @job_name = @jobName, @job_id = @job_id OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @job_id, @step_name = @jobName, @subsystem = N'TSQL', @command = @sql, @database_name = @dbname;

-- SQL Server 代理服务启动时运行
EXEC msdb.dbo.sp_add_jobschedule @job_id = @job_id, @name = @jobName, @enabled = 1, @freq_type = 64;

DECLARE @servername SYSNAME;
SET @servername = CONVERT(NVARCHAR(128), SERVERPROPERTY(N'ServerName'));
EXEC msdb.dbo.sp_add_jobserver @job_id = @job_id, @server_name = @servername;

GO

12. 查看所有作业 ​

SQL
SELECT      CASE WHEN job.enabled = 1 AND sch.enabled = 1 THEN N'启用'
                 ELSE
                   N'禁用（job-' + CASE job.enabled WHEN 0 THEN N'禁用' WHEN 1 THEN N'启用' ELSE N'未知（' + CONVERT(NVARCHAR(20), job.enabled) + N'）' END + ' sch-'
                   + CASE sch.enabled WHEN 0 THEN N'禁用' WHEN 1 THEN N'启用' ELSE N'未知（' + CONVERT(NVARCHAR(20), sch.enabled) + N'）' END + N'）'
            END enableState
          , job.name jobName
          --job.description jobDesc,
          , CASE sch.freq_type
                 WHEN 1 THEN N'仅一次'
                 WHEN 4 THEN N'每 ' + CONVERT(NVARCHAR(20), sch.freq_interval) + N' 日'
                 WHEN 8 THEN
                   N'每 ' + CONVERT(NVARCHAR(20), sch.freq_recurrence_factor) + N' 周的' + CASE WHEN sch.freq_interval & 1 = 1 THEN N'星期日、' ELSE N'' END
                   + CASE WHEN sch.freq_interval & 2 = 2 THEN N'星期一、' ELSE N'' END + CASE WHEN sch.freq_interval & 4 = 4 THEN N'星期二、' ELSE N'' END
                   + CASE WHEN sch.freq_interval & 8 = 8 THEN N'星期三、' ELSE N'' END + CASE WHEN sch.freq_interval & 16 = 16 THEN N'星期四、' ELSE N'' END
                   + CASE WHEN sch.freq_interval & 32 = 32 THEN N'星期五、' ELSE N'' END + CASE WHEN sch.freq_interval & 64 = 64 THEN N'星期六、' ELSE N'' END
                 WHEN 16 THEN N'每 ' + CONVERT(NVARCHAR(20), sch.freq_recurrence_factor) + N' 月的 ' + CONVERT(NVARCHAR(20), sch.freq_interval) + N' 日'
                 WHEN 32 THEN
                   N'每 ' + CONVERT(NVARCHAR(20), sch.freq_recurrence_factor) + N' 月' + CASE sch.freq_relative_interval
                                                                                            WHEN 1 THEN N'第一次'
                                                                                            WHEN 2 THEN N'第二次'
                                                                                            WHEN 4 THEN N'第三次'
                                                                                            WHEN 8 THEN N'第四次'
                                                                                            WHEN 16 THEN N'最后一次'
                                                                                            ELSE N'未知（' + CONVERT(NVARCHAR(20), sch.freq_relative_interval) + N'）'
                                                                                       END + N'的'
                   + CASE sch.freq_interval
                          WHEN 1 THEN N'星期日'
                          WHEN 2 THEN N'星期一'
                          WHEN 3 THEN N'星期二'
                          WHEN 4 THEN N'星期三'
                          WHEN 5 THEN N'星期四'
                          WHEN 6 THEN N'星期五'
                          WHEN 7 THEN N'星期六'
                          WHEN 8 THEN N'天'
                          WHEN 9 THEN N'工作日'
                          WHEN 10 THEN N'休息日'
                          ELSE N'未知（' + CONVERT(NVARCHAR(20), sch.freq_interval) + N'）'
                     END
                 WHEN 64 THEN N'SQL Server 代理服务启动时运行'
                 WHEN 128 THEN N'在计算机处于空闲状态时运行'
                 ELSE N'未知（' + CONVERT(NVARCHAR(20), sch.freq_type) + N'）'
            END schFreqType
          , CASE WHEN sch.freq_type IN (64, 128) THEN N''
                 WHEN sch.freq_type = 1 THEN N'在 ' + RIGHT(REPLICATE(N'0', 7) + CONVERT(NVARCHAR(8), sch.active_start_date), 8)
                 ELSE
                   N'从 ' + RIGHT(REPLICATE(N'0', 7) + CONVERT(NVARCHAR(8), sch.active_start_date), 8) + N' 到 '
                   + RIGHT(REPLICATE(N'0', 7) + CONVERT(NVARCHAR(8), sch.active_end_date), 8)
            END schDateFreq
          , CASE WHEN sch.freq_type IN (64, 128) THEN N''
                 WHEN sch.freq_type = 1 OR sch.freq_subday_type = 1 THEN N'在 ' + RIGHT(REPLICATE(N'0', 5) + CONVERT(NVARCHAR(6), sch.active_start_time), 6)
                 ELSE
                   N'在 ' + RIGHT(REPLICATE(N'0', 5) + CONVERT(NVARCHAR(6), sch.active_start_time), 6) + N' 到 '
                   + RIGHT(REPLICATE(N'0', 5) + CONVERT(NVARCHAR(6), sch.active_end_time), 6) + N' 之间，每 ' + CONVERT(NVARCHAR(20), sch.freq_subday_interval) + N' '
                   + CASE sch.freq_subday_type
                          WHEN 2 THEN N'秒'
                          WHEN 4 THEN N'分钟'
                          WHEN 8 THEN N'小时'
                          ELSE N'未知（' + CONVERT(NVARCHAR(20), sch.freq_subday_type) + N'）'
                     END
            END schDayFreq
          --CONVERT(CHAR(19), job.date_created, 120) jobCreated,
          --CONVERT(CHAR(19), job.date_modified, 120) jobModified,
          , CASE jobhis.run_status
                 WHEN 0 THEN N'失败'
                 WHEN 1 THEN N'成功'
                 WHEN 2 THEN N'重试'
                 WHEN 3 THEN N'取消'
                 WHEN 4 THEN N'进行中'
                 ELSE N'未知（' + CONVERT(NVARCHAR(20), jobhis.run_status) + N'）'
            END lastRunStates
          , jobhis.message lastRunMsg
          , RIGHT(REPLICATE('0', 7) + CONVERT(VARCHAR(8), jobhis.run_date), 8) + ' ' + RIGHT(REPLICATE('0', 5) + CONVERT(VARCHAR(6), jobhis.run_time), 6) lastRunDT
          , STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 7) + CONVERT(VARCHAR(8), jobhis.run_duration), 8), 3, 0, ' '), 6, 0, ':'), 9, 0, ':') lastRunDuration
          , jobstep.database_name jobStepDbName
          , jobstep.subsystem
          , jobstep.command jobStepCmd
          , CONCAT(jobstep.step_id, '：', jobstep.step_name) jobStepName
FROM        msdb..sysjobs job
INNER JOIN  msdb..sysjobsteps jobstep ON jobstep.job_id = job.job_id
INNER JOIN  msdb..sysjobschedules jobsch ON jobsch.job_id = job.job_id
INNER JOIN  msdb..sysschedules sch ON sch.schedule_id = jobsch.schedule_id
OUTER APPLY (
  SELECT   TOP 1
           jobhis.message, jobhis.run_status, jobhis.run_date, jobhis.run_time, jobhis.run_duration
  FROM     msdb..sysjobhistory jobhis
  WHERE    jobhis.job_id = job.job_id AND jobhis.step_id = jobstep.step_id
  ORDER BY jobhis.instance_id DESC
) jobhis
ORDER BY    job.name, jobstep.step_id;

转载请注明作者和出处

Copyright © 2025-至今 Tenormis