You want to quickly determine the next jobs to be executed on a busy
server. Perhaps you need to bring the
server down and you want to ensure you don't miss any important jobs. Or you are managing resources for the next couple of hours and you need to know which jobs to disable.
1st Solution
Your first approach is to query sysjobs and sysschedules.
-- Shows list of jobs and when they next run
SELECT j.name
JobName,
j.Enabled,
j.Description,
s.next_run_date,
s.next_run_time
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobschedules s
ON
s.job_id = j.job_id
ORDER BY next_run_date desc, next_run_time desc
Alas, while the above query gets you everything you need, the next TIME
for when a job is scheduled to run is formatted poorly. Therefore the ordering is incorrect.
Note the poorly formatted time below.
Ordering by next_run_time will not yield correct results. I added an additional column showing what the correctly formatted date/time should be.
Solution for formatting next run
date and time of jobs using a user-defined function
Use Admin
-- Use master
IF Object_ID('dbo.udfFormatJobNextRunDate')
Is Not Null
DROP
FUNCTION dbo.udfFormatJobNextRunDate
go
CREATE FUNCTION dbo.udfFormatJobNextRunDate(
@NextRunDateTEXT varchar(8),
@NextRunTimeTEXT varchar(6)
)
RETURNS Datetime
AS
/* DATE AUTHOR REMARKS
10/19/15 PPaiva Initial
creation
DESCRIPTION
Formats
the text columns Next_Run_Date and Next_Run_Time
in
sysJobSchedules to a datetime format.
TIME
formatting
Input:
4000
Output: 00:40:00
USAGE
SELECT *,
dbo.udfFormatJobNextRunDate(next_run_date,
next_run_time) ProperlyFormattedDatetime
FROM
msdb.dbo.sysJobSchedules
ORDER BY
len(next_run_time)
SELECT
dbo.udfFormatJobNextRunDate('20150401', '300')
SELECT
dbo.udfFormatJobNextRunDate('20150401', '2300')
SELECT
dbo.udfFormatJobNextRunDate('20150401', '12300')
SELECT
dbo.udfFormatJobNextRunDate('20150401', '112300')
*/
BEGIN
DECLARE
@Out datetime,
@NextRunTimeWithColons
varchar(8),
@LenTime int
--
Pad left with zeros
SET
@LenTime = Len(@NextRunTimeTEXT)
SET
@NextRunTimeTEXT = REPLICATE('0', 6 - @LenTime) + @NextRunTimeTEXT
--
Add colons. Change 005000 to 00:50:00
SET
@NextRunTimeWithColons = Left(@NextRunTimeTEXT,
2)
+ ':'
+ Substring(@NextRunTimeTEXT, 3, 2)
+ ':'
+ Right(@NextRunTimeTEXT,
2)
--
Concatenate date and time
SET
@Out = CASE WHEN IsNull(@NextRunDateTEXT, 0) = 0 THEN Null
ELSE Convert(datetime, @NextRunDateTEXT + ' ' +
@NextRunTimeWithColons
)
END
RETURN
@Out
END
2nd Solution
Now simply add the new function to the initial query.
-- Shows list of jobs and when they next run (modified
with solution)
-- Now jobs are correctly order by next run date/time
SELECT j.name
JobName,
j.Enabled,
j.Description,
s.next_run_date,
s.next_run_time,
dbo.udfFormatJobNextRunDate(next_run_date, next_run_time)
CorrectlyFormattedDatetime
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobschedules s
ON
s.job_id = j.job_id
ORDER BY 5 desc, 6 desc
