Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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,''

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
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.