November 3, 2015 at 1:02 pm
Hi All,
I need some help on below 2 queries.
1) List out all jobs which are scheduled for today along with the schedule details.
2) List out all jobs which are scheduled for next 5 hours
Thank you in advance.
November 3, 2015 at 1:22 pm
This might do it:
SELECT j.NAME
, s.next_run_time
FROM dbo.sysjobschedules s
CROSS APPLY (
SELECT datepart(YEAR, getdate()) * 10000 + datepart(MONTH, getdate()) * 100 + datepart(DAY, getdate()) AS today
, datepart(HOUR, getdate()) * 10000 + datepart(MINUTE, getdate()) * 100 + datepart(SECOND, getdate()) AS rightnow
) _
INNER JOIN dbo.sysjobs j
ON s.job_id = j.job_id
WHERE next_run_date = today
AND next_run_time BETWEEN rightnow
AND rightnow + 5 * 10000
just comment out the second part of the where for all jobs scheduled today
Gerald Britton, Pluralsight courses
November 3, 2015 at 2:12 pm
Thats Awesome. Thank you Gerald. This helps 🙂
November 3, 2015 at 2:20 pm
Hi Gerald,
1 last question,
1. Suppose if I want to list out the jobs which has to run on a <<specific date>>, how to pull that info using tsql query?
2. Suppose if I want to list out jobs which has supposed to run between 2 dates i.e. I will specify the date range , then how ?
Thanks in advance.
November 3, 2015 at 2:25 pm
vsamantha35 (11/3/2015)
Hi Gerald,1 last question,
1. Suppose if I want to list out the jobs which has to run on a <<specific date>>, how to pull that info using tsql query?
2. Suppose if I want to list out jobs which has supposed to run between 2 dates i.e. I will specify the date range , then how ?
Thanks in advance.
Change the where clauses to suit. They're not difficult
Gerald Britton, Pluralsight courses
November 3, 2015 at 2:36 pm
Ok Gerald I will try. Basically I am not a TSQL expert or from programming back ground.
November 3, 2015 at 11:41 pm
Finally, was able to the query Gerald. Thanks for the help.
declare @dt1 datetime
declare @dt2 datetime
set @dt1 = GETDATE()
set @dt2 = GETDATE()+1
select distinct name,description,
convert(varchar(20),next_scheduled_run_date,100) as next_scheduled_run_date
from msdb.dbo.sysjobs sj
join msdb.dbo.sysjobactivity sa
on sj.job_id =sa.job_id
where enabled = 1 AND
next_scheduled_run_date between @dt1 AND @dt2
order by 3
November 4, 2015 at 3:38 pm
note, instead of the calculations, you can use CONVERT with format 112 to get the format for a date, and then cast that as an integer in the WHERE clause.
November 9, 2015 at 1:46 pm
g.britton (11/3/2015)
This might do it:
SELECT j.NAME, s.next_run_time
FROM dbo.sysjobschedules s
CROSS APPLY (
SELECT datepart(YEAR, getdate()) * 10000 + datepart(MONTH, getdate()) * 100 + datepart(DAY, getdate()) AS today
, datepart(HOUR, getdate()) * 10000 + datepart(MINUTE, getdate()) * 100 + datepart(SECOND, getdate()) AS rightnow
) _
INNER JOIN dbo.sysjobs j
ON s.job_id = j.job_id
WHERE next_run_date = today
AND next_run_time BETWEEN rightnow
AND rightnow + 5 * 10000
just comment out the second part of the where for all jobs scheduled today
Note: if you want to easily code around the problem caused by msdb storing job dates and times as integers in two separate columns, use this:
dbo.agent_datetime(s.next_run_date, s.next_run_time) As NextRunDateTime
which will return a DATETIME data type. If you have any schedules where next_run_date = 0, you'll get an error, so exclude them in the WHERE clause.
Rich
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply