How to list scheduled jobs with jobstep

[Gunnien]

Hello,

I am quiet new to SQL-server but I have got most of the responsibility for our datawearehouse. Most of the serverjobs are made by a tool layed on top of the server called "BPW designer", evry ETL job made by this tool are put as a job on the SQL-server. Each of this jobs contains several jobsteps, and a lot of these steps are runing several times during a night.

So in order to delete some jobs that are running more than one time each night, I will need a list of every job that are running on this server with the schedule and every steps these jobs contains. This list also has to be sorted by the scheduled time they will start.

So my question to some of you gurus out there are:

Do a SQL-server have the opportunity to give me this list in an easy way and how?

Thanks in advance.


Gunnien

 

[nigelrivett]

This will give you the jobs and steps.
Look at the tables for the other attributes you might need.

select jobname = j.name, StepName = js.step_name
from sysjobs j
join sysjobsteps js
on js.job_id = j.job_id
order by j.name, js.step_name


Getting the next run time isn't so easy because on first run you will have to go through all the schedules and calculate when that will be.

This is some code that I run to save the job history on a daily basis so that it doesn't dissappear due to the history being truncated.
I save it into an admin database so that archives can be restored to see what happenned in the past.

Create procedure s_SaveJobHistory
as

declare @startdate datetime
select @startdate = convert(varchar(8),dateadd(dd, -2, getdate()),112)

insert JobHistory
(
job_id ,
originating_server ,
Server ,
job_name ,
date_created ,
date_modified ,
instance_id ,
step_id ,
step_name ,
run_endtime ,
run_duration ,
run_status ,
message
)
select j.job_id ,
j.originating_server ,
'mphamrsql02' ,
j.name ,
j.date_created ,
j.date_modified ,
h.instance_id ,
h.step_id ,
h.step_name ,
run_endtime = convert(varchar(8),h.run_date) + ' ' + stuff(stuff(right('000000' + convert(varchar(6),h.run_time), 6),5,0,':'),3,0,':') ,
run_duration = stuff(stuff(right('000000' + convert(varchar(6),h.run_duration), 6),5,0,':'),3,0,':') ,
run_status = case h.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'InProgress'
end ,
h.message
from msdb..sysjobs j
join msdb..sysjobhistory h
on h.job_id = j.job_id
where not exists
( select *
from JobHistory jh
where jh.job_id = j.job_id
and jh.step_id = h.step_id
and jh.instance_id = h.instance_id
and jh.run_endtime = convert(varchar(8),h.run_date) + ' ' + stuff(stuff(right('000000' + convert(varchar(6),h.run_time), 6),5,0,':'),3,0,':')
and jh.run_duration = stuff(stuff(right('000000' + convert(varchar(6),h.run_duration), 6),5,0,':'),3,0,':')
)
and @startdate < convert(varchar(8),h.run_date) + ' ' + stuff(stuff(right('000000' + convert(varchar(6),h.run_time), 6),5,0,':'),3,0,':')

-- archive table so it doesn't get too big
begin tran
insert JobHistoryArchive
(
job_id ,
originating_server ,
Server ,
job_name ,
date_created ,
date_modified ,
instance_id ,
step_id ,
step_name ,
run_endtime ,
run_duration ,
run_status ,
message
)
select job_id ,
originating_server ,
Server ,
job_name ,
date_created ,
date_modified ,
instance_id ,
step_id ,
step_name ,
run_endtime ,
run_duration ,
run_status ,
message
from JobHistory
where run_endtime < @startdate

if @@error <> 0
begin
rollback tran
raiserror('failed insert JobHistoryArchive', 16, -1)
return
end

delete JobHistory
where run_endtime < @startdate

if @@error <> 0
begin
rollback tran
raiserror('failed delete JobHistory', 16, -1)
return
end
commit tran

GO

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Gunnien]

Hi nigelrivett ,

Thanks a lot for your help. But when I posted this question I started to dig a litle on the server and found the sysjobs, Sysjobschedules and sysjobsteps tables. here I wrote a small SQL-suntax and got everythisn I needed.

The syntax are as simple as this:

SELECT A.[Originating_Server] AS [Server], A.[Name] AS [Jobname], A.[Description] AS [Desc], B.[name] AS [Shedulename], CASE WHEN (B.[Freq_Type] = 1) THEN 'Once' WHEN (B.[Freq_Type] = 4) THEN 'Dayly' WHEN (B.[Freq_Type] = 8) THEN 'Weekly' WHEN (B.[Freq_Type] = 16) THEN 'Monthly fixed day' WHEN (B.[Freq_Type] = 32) THEN 'Monthly relative' ELSE 'When SQLserver agent starts' END AS [Frequency type], B.[Next_Run_Date] AS [Next run date], B.[Next_Run_time] AS [Next run time], B.[Date_Created] AS [Created date], C.[Step_Id] AS [Step no], C.[Step_Name] AS [Step name], C.[SubSystem] AS [Sub system name], C.[Command] AS [Command name]
FROM ([Sysjobs] A INNER JOIN [Sysjobschedules] B ON A.[Job_Id] = B.[Job_Id]) INNER JOIN [Sysjobsteps] C ON B.[Job_Id] = C.[Job_Id]

If anyone else would need something like this.

 

[nigelrivett]

The problem you will have is the ordering bit and Next run date is not filled in when the schedule is created.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Gunnien]

Yes, but if I &quot;ORDER BY B.[Freq_Type], B.[Next_Run_time] ASC&quot; then I will get a good image of the jobs end wich steps that these jobs contain.