Current State of Scheduled Jobs
Published: 5th June 2014
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,''

select j.name,
      
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
Comments:
NB: Comments will only appear once they have been moderated.