December 16, 2003 at 1:43 pm
Does anyone have any utility procedure to find the SQL jobs that
are scheduled to run between any two variable timeframes? The next run date
in the SQL EM Job Details would not work, since it is just the next run date.
For example, let us say I have a job that runs every 2 hours, and let's say the next
run date is 4 PM. If I need to find the jobs running between
9 and 11 PM, this job would run during that time (at 10 PM, every 2 hours) and hence needs to be
reported. I would appreciate is anyone has any script to do so. Though I could query
the system tables in msdb, I would prefer not reinvent the wheel if someone has already
done so.
thanks.
December 16, 2003 at 3:47 pm
Interesting. This could easily get confusing to read. Are you looking for a query that reports all jobs that "potentially" will run between some time frame?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 16, 2003 at 5:58 pm
Hello, the following script has not been fully tested. But you get the point, you can do your own debugging.
-------------------------------------------
CREATE PROCEDURE dbo.ScheduledJobs
( @Startdate smalldatetime,
@EndDate smalldatetime
)
AS
declare @tmpStartDate char(10)
declare @tmpEndDate char(10)
declare @tmpStartTime char(8)
declare @tmpEndTime char(8)
declare @myStartDate int
declare @myEndDate int
declare @myStartTime int
declare @myEndTime int
set @tmpStartDate = convert(char(10), @Startdate, 101)
set @tmpEndDate = convert(char(10), @EndDate, 101)
set @tmpStartTime = convert(char(8), @StartDate, 8)
set @tmpEndTime = convert(char(8), @EndDate, 8)
set @myStartDate = cast(right(@tmpStartDate, 4) + left(@tmpStartDate, 2) + substring(@tmpStartDate, 4, 2) as int)
set @myEndDate = cast(right(@tmpEndDate, 4) + left(@tmpEndDate, 2) + substring(@tmpEndDate, 4, 2) as int)
set @myStartTime = cast(left(@tmpStartTime, 2) + substring(@tmpStartTime, 4,2) + right(@tmpStartTime, 2) as int)
set @myEndTime = cast(left(@tmpEndTime, 2) + substring(@tmpEndTime, 4,2) + right(@tmpEndTime, 2) as int)
if @myEndTime = 0
set @myEndTime = 235959
select j.name,
null as step_name,
s.next_run_date as run_date,
s.next_run_time as run_time
from msdb..sysjobschedules s
inner join msdb..sysjobs j
on s.Job_id = j.job_id
where next_run_date >= @myStartDate
and next_run_date <= @myEndDate
and next_run_time >= @myStartTime
and next_run_time <= @myEndTime
UNION
select j.name,
step_name,
h.run_date,
h.run_time
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.Job_id = j.job_id
where run_date >= @myStartDate
and run_date <= @myEndDate
and run_time >= @myStartTime
and run_time <= @myEndTime
and Step_id <> 0
order by j.name, run_date, run_time
December 16, 2003 at 7:28 pm
Found bugs....
-----------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.ScheduledJobs
( @Startdate smalldatetime,
@EndDate smalldatetime
)
AS
declare @tmpStartDate char(10)
declare @tmpEndDate char(10)
declare @tmpStartTime char(8)
declare @tmpEndTime char(8)
declare @myStartDate int
declare @myEndDate int
declare @myStartTime int
declare @myEndTime int
set @tmpStartDate = convert(char(10), @Startdate, 101)
set @tmpEndDate = convert(char(10), @EndDate, 101)
set @tmpStartTime = convert(char(8), @StartDate, 8)
set @tmpEndTime = convert(char(8), @EndDate, 8)
set @myStartDate = cast(right(@tmpStartDate, 4) + left(@tmpStartDate, 2) + substring(@tmpStartDate, 4, 2) as int)
set @myEndDate = cast(right(@tmpEndDate, 4) + left(@tmpEndDate, 2) + substring(@tmpEndDate, 4, 2) as int)
set @myStartTime = cast(left(@tmpStartTime, 2) + substring(@tmpStartTime, 4,2) + right(@tmpStartTime, 2) as int)
set @myEndTime = cast(left(@tmpEndTime, 2) + substring(@tmpEndTime, 4,2) + right(@tmpEndTime, 2) as int)
if @myEndTime = 0
set @myEndTime = 235959
select j.name,
null as step_name,
s.next_run_date as run_date,
s.next_run_time as run_time
from msdb..sysjobschedules s
inner join msdb..sysjobs j
on s.Job_id = j.job_id
where ((next_run_date = @myStartDate and next_run_time >= @myStartTime)
or next_run_date > @myStartDate)
and ((next_run_date <= @myEndDate and next_run_time <= @myEndTime)
or next_run_date < @myEndDate )
UNION
select j.name,
step_name,
h.run_date,
h.run_time
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.Job_id = j.job_id
where ((run_date >= @myStartDate and run_time >= @myStartTime)
or run_Date > @myStartDate )
and ((run_date <= @myEndDate and run_time <= @myEndTime)
or run_date < @myEndDate)
and Step_id <> 0
order by j.name, run_date, run_time
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Edited by - tikus on 12/16/2003 7:39:44 PM
December 17, 2003 at 8:00 am
This procedure shows history between two runtimes. I am looking for something that would show
the jobs sheduled to run between provided timeframes in the future.
I appreciate your time.
Thanks.
December 17, 2003 at 12:01 pm
The first select statement shows the NEXT run date for the Jobs. The second select statement shows the history.
To show all the scheduled date/time, not just the NEXT run date, I guess you have to play around with the sysjobschedules table in msdb e.g. the freq_type, freq_interval fields should give you the infor.
Edited by - tikus on 12/17/2003 12:26:32 PM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply