SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Job to list all the Sql Server Agent Jobs

on February 9, 2012

with JobStatus as

(

select

job_id AS JobId,

row_number() over(partition by job_id order by run_date desc, run_time desc) asExecutionRank,

case run_status when 0 then 'Failed' when 1 then 'Succeeded' when 2 then 'Retry' when3 then 'Canceled' end as LastRunStatus

from

msdb.dbo.sysjobhistory

)

select

j.job_id as JobId,

j.name as JobName,

j.[description] as JobDescription,

j.[enabled] as IsJobEnabled,

js.LastRunStatus,

ja.run_requested_date as LastRunDate,

ja.next_scheduled_run_date as NextRunDate,

datediff(second, ja.start_execution_date, ja.stop_execution_date) as JobDuration

from

msdb.dbo.sysjobs j

join msdb.dbo.sysjobactivity ja on j.job_id = ja.job_id

left join JobStatus js on j.job_id = js.JobId

and js.ExecutionRank = 1

order by

JobName,

NextRunDate
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: