Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


List All Jobs and Their Schedules


List All Jobs and Their Schedules

Author
Message
Mike Pearson
Mike Pearson
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 1
Comments posted to this topic are about the item List All Jobs and Their Schedules



Sergey Kazachenko
Sergey Kazachenko
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 101
Do you have the same for SQL2005?
agonzal2
agonzal2
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 18
Same SQL statement Mike wrote but adapted to SQL Server 2005 and also displaying subday frequencies for those jobs that run multiples times a day.


/*******************************************************************************

Name: GetJobSchedule (For SQL Server7.0&2000)

Author: M.Pearson
Creation Date: 5 Jun 2002
Version: 1.0


Program Overview: This queries the sysjobs, sysjobschedules and sysjobhistory table to
produce a resultset showing the jobs on a server plus their schedules
(if applicable) and the maximun duration of the job.

The UNION join is to cater for jobs that have been scheduled but not yet
run, as this information is stored in the 'active_start...' fields of the
sysjobschedules table, whereas if the job has already run the schedule
information is stored in the 'next_run...' fields of the sysjobschedules table.


Modification History:
-------------------------------------------------------------------------------
Version Date Name Modification
-------------------------------------------------------------------------------
1.0 5 Jun 2002 M.Pearson Inital Creation
1.1 6 May 2009 A. Gonzalez Adapted to SQL Server 2005 and to show
subday frequencies.

*******************************************************************************/


USE msdb
Go


SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.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,
'Start Date' = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + '/' +
substring(convert(varchar(15),active_start_date),5,2) + '/' +
substring(convert(varchar(15),active_start_date),7,2)
END,
'Start Time' = CASE len(active_start_time)
WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
WHEN 3 THEN cast('00:0'
+ Left(right(active_start_time,3),1)
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(active_start_time,5),1)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
END,
-- active_start_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ' '
+ CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0

UNION

SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.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,
'Start Date' = 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,
'Start Time' = 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,
-- next_run_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ' '
+ CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0

ORDER BY [Start Date],[Start Time]


Jaybrown845
Jaybrown845
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 13
@agonzal2

modified it, see comments in the code



/*******************************************************************************

Name: GetJobSchedule (For SQL Server7.0&2000)

Author: M.Pearson
Creation Date: 5 Jun 2002
Version: 1.0


Program Overview: This queries the sysjobs, sysjobschedules and sysjobhistory table to
produce a resultset showing the jobs on a server plus their schedules
(if applicable) and the maximun duration of the jojob.

The UNION join is to cater for schedules not yet
run, as this information is stored in the 'active_start...' fields of the
sysjobschedules table, whereas if the job has already run the schedule
information is stored in the 'next_run...' fields of the sysjobschedules table.

Note: when a job has multiple schedules it appears multiple itmes in the list


Modification History:
-------------------------------------------------------------------------------
Version Date Name Modification
-------------------------------------------------------------------------------
1.0 5 Jun 2002 M.Pearson Inital Creation
1.1 6 May 2009 job_schd. Gonzalez Adapted to SQL Server 2005 and to show
subday frequencies.

1.2 7 Jul 2016 Jaybrown845 use field Next_run_date instead of Next_run_time to determine lookup to active_start_date field (when using field Next_run_time it does not show next run time for schedules running at 12 AM)
changed join to a left join to include jobs witout any schedule
Added fileds: schedule id, scheduled enabled, schedule end date and time, job fail notify mail and email
combined seperated queries into a single query
prepared fo view creation

reformatted
*******************************************************************************/


USE msdb
GO


CREATE VIEW dbo.vw_job_and_schedules

AS

SELECT
[Job ID] = job.job_id
,[Job Name] = job.Name
,[Job Enabled] =
CASE job.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
,[Sched ID] = sched.schedule_id
,[Sched Enabled] =
CASE sched.enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
,[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
,[Next Run Date] =
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
,[Next Run Time] =
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
,[Max Duration] =
CASE LEN(run_duration)
WHEN 1 THEN CAST('00:00:0'
+ CAST(run_duration AS CHAR) AS CHAR(8))
WHEN 2 THEN CAST('00:00:'
+ CAST(run_duration AS CHAR) AS CHAR(8))
WHEN 3 THEN CAST('00:0'
+ LEFT(RIGHT(run_duration, 3), 1)
+ ':' + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 4 THEN CAST('00:'
+ LEFT(RIGHT(run_duration, 4), 2)
+ ':' + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 5 THEN CAST('0'
+ LEFT(RIGHT(run_duration, 5), 1)
+ ':' + LEFT(RIGHT(run_duration, 4), 2)
+ ':' + RIGHT(run_duration, 2) AS CHAR(8))
WHEN 6 THEN CAST(LEFT(RIGHT(run_duration, 6), 2)
+ ':' + LEFT(RIGHT(run_duration, 4), 2)
+ ':' + RIGHT(run_duration, 2) AS CHAR(8))
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 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END AS CHAR(16))
END
,[Sched End Date] = sched.active_end_date
,[Sched End Time] = sched.active_end_time
,[Fail Notify Name] =
CASE
WHEN oper.enabled = 0 THEN 'Disabled: '
ELSE ''
END + oper.name
,[Fail Notify Email] = oper.email_address

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


amruta.kar1982
amruta.kar1982
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 90
Beautiful code
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search