Current State of Scheduled Jobs
Published: Dec 11, 2015
This is another useful piece of code (well, I find it handy anyway) which I wrote to help populate a dashboard.

It’s very simple but, based on a quick internet search, fulfils a gap people seem to struggle with.

Basically it provides very simple information about the scheduled jobs on your SQL Server covering the job name, last and next run times, the last outcome, and the current state (running or not). I also added an isEnabled flag.

As mentioned, the code itself is pretty simple using the undocumented (therefore there’s a caveat here… it’s undocumented and therefore MIGHT not be reliable in future versions) xp_sqlAgent_enum_jobs internal procedure.

It does exactly as mentioned above… feel free to use it as you please (as I’ve seen a lot of people on the internet struggling to find this information - specifically whether a job is currently running or not).

declare @CurrentJobs table
jobID uniqueidentifier,
lastRunDate varchar(255),
lastRunTime varchar(255),
nextRunDate varchar(255),
nextRunTime varchar(255),
nextScheduleID varchar(255),
requestedToRun varchar(255),
requestSource varchar(255),
requestSourceID varchar(255),
running varchar(255),
currentStep varchar(255),
currentRetryAttempt varchar(255),
jobState varchar(255)
insert into @CurrentJobs
exec master.dbo.xp_sqlagent_enum_jobs 1,''

case lastRunDate
when 0 then null
else convert(smalldatetime, left(c.lastRunDate, 4) + '-' +
substring(c.lastRunDate, 5, 2) + '-' + right(c.lastRunDate, 2))
end lastRun,
case nextRunDate
when 0 then null
else convert(smalldatetime, left(c.nextRunDate, 4) + '-' +
substring(c.nextRunDate, 5, 2) + '-' + right(c.nextRunDate, 2))
end nextRun,
case v.last_run_outcome
when 0 then 'Failed'
when 1 then 'Succeeded'
when 3 then 'Cancelled'
when 5 then 'Never Run'
end lastRunOutcome,
c.running isRunning, enabled isEnabled
from @CurrentJobs c
join msdb.dbo.sysjobs j
on c.jobID = j.job_id
join msdb.dbo.sysjobservers v
on j.job_id = v.job_id
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron