SQL Agent Jobs - Enable, Disable, Stop - Commands
USE msdb
SELECT [SQL Server Name] = @@SERVERNAME,
[Job Name] = job.NAME,
[Sched Frequency] = CASE sched.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
[Subday Frequency] = CASE ( sched.freq_subday_interval )
WHEN 0 THEN 'Once'
ELSE Cast('Every '
+ RIGHT(sched.freq_subday_interval, 2) +
' '
+ CASE (sched.freq_subday_type) WHEN 1
THEN
'Once' WHEN 2 THEN
'Seconds'
WHEN 4
THEN 'Minutes' WHEN 8 THEN 'Hours' END
AS CHAR(
16))
END,
[Next Run Time] = Cast(CASE next_run_date WHEN 0 THEN NULL ELSE Substring
(
CONVERT(VARCHAR(15),
next_run_date), 1, 4) + '/' + Substring(CONVERT(
VARCHAR(
15), next_run_date)
, 5,
2) + '/' + Substring(CONVERT(VARCHAR(15),
next_run_date),
7, 2) END + ' ' +
CASE
Len(next_run_time) WHEN 1 THEN Cast('00:00:0' +
RIGHT(
next_run_time, 2) AS
CHAR(
8)) WHEN 2 THEN Cast('00:00:' + RIGHT(
next_run_time, 2)
AS CHAR(8)) WHEN 3
THEN
Cast('00:0' + LEFT(RIGHT(next_run_time, 3), 1) +
':' +
RIGHT(next_run_time,
2)
AS CHAR(8)) WHEN 4 THEN Cast('00:' + LEFT(RIGHT(
next_run_time, 4), 2) + ':'
+
RIGHT(next_run_time, 2) AS CHAR(8)) WHEN 5 THEN
Cast('0'
+ LEFT(RIGHT(
next_run_time, 5), 1) + ':' + LEFT(RIGHT(
next_run_time, 4
), 2) + ':' +
RIGHT(
next_run_time, 2) AS CHAR(8)) WHEN 6 THEN Cast(
LEFT(RIGHT
(next_run_time, 6)
, 2)
+ ':' + LEFT(RIGHT(next_run_time, 4), 2) + ':' +
RIGHT(
next_run_time, 2) AS
CHAR
(8)) END AS DATETIME),
[Job Disable Command] = 'EXEC msdb.dbo.sp_update_job @job_name='''
+ job.NAME + ''',@enabled = 0;',
[Job Enable Command] = 'EXEC msdb.dbo.sp_update_job @job_name='''
+ job.NAME + ''',@enabled = 1;',
[Job Stop Command] = CASE sched.freq_type
WHEN 64 THEN 'EXEC msdb.dbo.sp_stop_job @job_name='''
+ job.NAME + ''';'
ELSE ''
END
FROM dbo.sysjobs job
LEFT JOIN (SELECT job_schd.job_id,
sys_schd.enabled,
sys_schd.schedule_id,
sys_schd.freq_type,
sys_schd.freq_subday_type,
sys_schd.freq_subday_interval,
next_run_date = CASE
WHEN job_schd.next_run_date = 0 THEN
sys_schd.active_start_date
ELSE job_schd.next_run_date
END,
next_run_time = CASE
WHEN job_schd.next_run_date = 0 THEN
sys_schd.active_start_time
ELSE job_schd.next_run_time
END,
active_end_date = NULLIF(sys_schd.active_end_date,
'99991231')
,
active_end_time =
NULLIF(sys_schd.active_end_time, '235959')
FROM dbo.sysjobschedules job_schd
LEFT JOIN dbo.sysschedules sys_schd
ON job_schd.schedule_id = sys_schd.schedule_id)
sched
ON job.job_id = sched.job_id
LEFT OUTER JOIN (SELECT job_id,
Max(job_his.run_duration) AS run_duration
FROM dbo.sysjobhistory job_his
GROUP BY job_id) Q1
ON job.job_id = Q1.job_id
LEFT JOIN sysoperators oper
ON job.notify_email_operator_id = oper.id
WHERE job.enabled = 1
AND sched.enabled = 1
ORDER BY Cast(CASE next_run_date WHEN 0 THEN NULL ELSE Substring(CONVERT(
VARCHAR(15),
next_run_date), 1, 4) + '/' + Substring(CONVERT(VARCHAR(15),
next_run_date)
, 5,
2) + '/' + Substring(CONVERT(VARCHAR(15), next_run_date), 7, 2)
END + ' ' +
CASE
Len(next_run_time) WHEN 1 THEN Cast('00:00:0' + RIGHT(
next_run_time, 2) AS
CHAR(
8)) WHEN 2 THEN Cast('00:00:' + RIGHT(next_run_time, 2) AS CHAR(8
)) WHEN 3
THEN
Cast('00:0' + LEFT(RIGHT(next_run_time, 3), 1) + ':' + RIGHT(
next_run_time,
2)
AS CHAR(8)) WHEN 4 THEN Cast('00:' + LEFT(RIGHT(next_run_time, 4)
, 2) + ':'
+
RIGHT(next_run_time, 2) AS CHAR(8)) WHEN 5 THEN Cast('0' + LEFT(
RIGHT(
next_run_time, 5), 1) + ':' + LEFT(RIGHT(next_run_time, 4), 2) +
':' +
RIGHT(
next_run_time, 2) AS CHAR(8)) WHEN 6 THEN Cast(LEFT(RIGHT(
next_run_time, 6)
, 2)
+ ':' + LEFT(RIGHT(next_run_time, 4), 2) + ':' + RIGHT(
next_run_time, 2) AS
CHAR
(8)) END AS DATETIME),
Job.NAME
|
|
|
|
|
|