November 17, 2008 at 11:04 pm
Hi,
Is there any query which can provide me with the list of scheduled job names, run time and frequently of running.
Thanks for your replies.
Cheers
A.
November 18, 2008 at 8:48 am
You could write a query that joins msdb.dbo.sysjobs and msdb.dbo.sysjobschedules or run sp_help_job and sp_help_jobschedule.
Greg
November 18, 2008 at 8:54 am
I don't have a code handy for SQL 2000
but this one works in 2005 & 2008
--Author: Saleem Hakani (Microsoft Corporation)
--Email: Saleem@sqlcommunity.com
--Website: http://www.sqlcommunity.com
--Date: June 3rd 2008
--Purpose: Provide SQL Server Agent related job information
SELECT DISTINCT substring(a.name,1,100) AS [Job Name],
'Enabled'=case
WHEN a.enabled = 0 THEN 'No'
WHEN a.enabled = 1 THEN 'Yes'
end,
substring(b.name,1,30) AS [Name of the schedule],
'Frequency of the schedule execution'=case
WHEN b.freq_type = 1 THEN 'Once'
WHEN b.freq_type = 4 THEN 'Daily'
WHEN b.freq_type = 8 THEN 'Weekly'
WHEN b.freq_type = 16 THEN 'Monthly'
WHEN b.freq_type = 32 THEN 'Monthly relative'
WHEN b.freq_type = 32 THEN 'Execute when SQL Server Agent starts'
END,
'Units for the freq_subday_interval'=case
WHEN b.freq_subday_type = 1 THEN 'At the specified time'
WHEN b.freq_subday_type = 2 THEN 'Seconds'
WHEN b.freq_subday_type = 4 THEN 'Minutes'
WHEN b.freq_subday_type = 8 THEN 'Hours'
END,
cast(cast(b.active_start_date as varchar(15)) as datetime) as active_start_date,
cast(cast(b.active_end_date as varchar(15)) as datetime) as active_end_date,
cast(cast(c.next_run_date as varchar(15)) as datetime) as next_run_date,
Stuff(Stuff(right('000000'+Cast(c.next_run_time as Varchar),6),3,0,':'),6,0,':') as Run_Time,
b.date_created
FROM msdb..sysjobhistory d
INNER JOIN msdb..sysjobs a ON a.job_id = d.job_id
INNER JOIN msdb..sysJobschedules c ON a.job_id = c.job_id
INNER JOIN msdb..SysSchedules b on b.Schedule_id=c.Schedule_id
GO
Output
Job NameEnabledName of the scheduleFrequency of the schedule executionUnits for the freq_subday_intervalactive_start_dateactive_end_datenext_run_dateRun_Timedate_created
syspolicy_purge_historyYessyspolicy_purge_history_scheduDailyAt the specified time2008-01-01 00:00:00.0009999-12-31 00:00:00.0002008-11-19 00:00:00.00002:00:002008-10-20 15:08:49.377
November 18, 2008 at 8:59 am
other than that you also have
msdb..sp_help_job
(and also sp_help_job_schedule 'myjobname')
MVDBA
November 19, 2008 at 10:10 pm
Hi All,
Thanks for your replies. I got the idea.
Cheers
A.
January 7, 2009 at 2:41 pm
Jerry Hung (11/18/2008)
I don't have a code handy for SQL 2000but this one works in 2005 & 2008
(snipped)
Output
Thanks for this answer, with some mods, this helped with some detail today with an automated management question.
September 5, 2014 at 3:57 pm
Here it is with dates formatted and ordered by freq and next run date & time. This works also in SQL Server 2012. (Darn, when I edited it, it lost all formatting. You'll have to reformat using either SQL Server 2012 Tools or Poor Man's T-SQL Formatter add-in for Notepad++)
-- Henry Stinson, DBA
--Purpose: Provide SQL Server Agent related job information
SELECT DISTINCT substring(a.name,1,100) AS [Job Name],
'Enabled'=case
WHEN a.enabled = 0 THEN 'No'
WHEN a.enabled = 1 THEN 'Yes'
end,
substring(b.name,1,30) AS [Name of the schedule],
'Frequency of the schedule execution'=case
WHEN b.freq_type = 1 THEN 'Once'
WHEN b.freq_type = 4 THEN 'Daily'
WHEN b.freq_type = 8 THEN 'Weekly'
WHEN b.freq_type = 16 THEN 'Monthly'
WHEN b.freq_type = 32 THEN 'Monthly relative'
WHEN b.freq_type = 32 THEN 'Execute when SQL Server Agent starts'
END,
'Units for the freq_subday_interval'=case
WHEN b.freq_subday_type = 1 THEN 'At the specified time'
WHEN b.freq_subday_type = 2 THEN 'Seconds'
WHEN b.freq_subday_type = 4 THEN 'Minutes'
WHEN b.freq_subday_type = 8 THEN 'Hours'
END,
SUBSTRING(CAST(b.active_start_date AS VARCHAR(8)), 5, 2) + '-' + SUBSTRING(CAST(b.active_start_date AS VARCHAR(8)), 7, 2) + '-' + SUBSTRING(CAST(b.active_start_date AS VARCHAR(8)), 1, 4) As ActiveStartDate,
--b.active_end_date,
SUBSTRING(CAST(c.next_run_date AS VARCHAR(8)), 5, 2) + '-' + SUBSTRING(CAST(c.next_run_date AS VARCHAR(8)), 7, 2) + '-' + SUBSTRING(CAST(c.next_run_date AS VARCHAR(8)), 1, 4) As NextRunDate,
--cast(cast(b.active_start_date as varchar(15)) as datetime) as active_start_date,
--cast(cast(b.active_end_date as varchar(15)) as datetime) as active_end_date,
--cast(cast(c.next_run_date as varchar(15)) as datetime) as next_run_date,
Stuff(Stuff(right('000000'+Cast(c.next_run_time as Varchar),6),3,0,':'),6,0,':') as Run_Time,
b.date_created
FROM msdb..sysjobhistory d
INNER JOIN msdb..sysjobs a
ON a.job_id = d.job_id
INNER JOIN msdb..sysJobschedules c
ON a.job_id = c.job_id
INNER JOIN msdb..SysSchedules b
ON b.Schedule_id=c.Schedule_id
ORDER BY [Frequency of the schedule execution],
[NextRunDate],
[Run_Time];
GO
July 14, 2017 at 6:52 am
I realize this is a very old thread but thought I'd throw this out there in case someone else has experienced it.
This code is almost exactly what I am looking for! Thank you! Unfortunately it returns, on one of my servers, 84 rows but when I count them manually, there are 113 jobs on that server. I'm still digging through which ones are missing but I'm sure it has to do with the inner joins narrowing my search down.
Has anyone tweaked this to work to show ALL the jobs on a server?
Looks like someone already answered my question. Thanks!! 😀
Thanks, Henry!!
July 14, 2017 at 6:57 am
Yes, it'll only return jobs that are scheduled. Change the INNER JOIN between sysjobs and sysjobschedules to a LEFT JOIN to see all jobs.
John
July 14, 2017 at 6:57 am
the first table should be sysjobs, as some jobs may exist but have NEVER been executed,and thus have no history.
also, the joins should be LEFT JOINs, since jobs could exist, but have no schedules.
another issue, which is more of a "I expected that", is i have a couple jobs with multiple schedules, so they appear twice.
FROM msdb..sysjobs a
LEFT JOIN msdb..sysjobhistory d
ON a.job_id = d.job_id
LEFT JOIN msdb..sysJobschedules c
ON a.job_id = c.job_id
LEFT JOIN msdb..SysSchedules b
ON b.Schedule_id=c.Schedule_id
ORDER BY [Frequency of the schedule execution],
[NextRunDate],
[Run_Time];
Lowell
July 14, 2017 at 7:12 am
John Waclawski - Friday, July 14, 2017 6:52 AMI realize this is a very old thread but thought I'd throw this out there in case someone else has experienced it.This code is almost exactly what I am looking for! Thank you! Unfortunately it returns, on one of my servers, 84 rows but when I count them manually, there are 113 jobs on that server. I'm still digging through which ones are missing but I'm sure it has to do with the inner joins narrowing my search down.
Has anyone tweaked this to work to show ALL the jobs on a server?Thanks, Henry!!
It's for scheduled jobs. If you look at the joins, the jobs would need a schedule as well as history to show up. If you wanted every job, not just scheduled jobs, try changing the joins to this to get every job:
FROM msdb..sysjobhistory d
RIGHT JOIN msdb..sysjobs a
ON a.job_id = d.job_id
LEFT JOIN msdb..sysJobschedules c
ON a.job_id = c.job_id
LEFT JOIN msdb..SysSchedules b
ON b.Schedule_id=c.Schedule_id
ORDER BY [Job Name]
Sue
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply