SQL QUERY HELP!!!! (SQL agent job information)

[mec01]

SELECT DISTINCT substring(SYSJOBS.name,1,100) AS [Job Name], SYSJOBSTEPS.step_name AS JobStep,
SYSCATEGORIES.name AS category, SYSJOBS.description as JobDescription, SYSJOBS.date_created as CreateDate,
'Enabled'=case
WHEN SYSSCHEDULES.enabled = 0 THEN 'DISABLED'
WHEN SYSSCHEDULES.enabled = 1 THEN 'ENABLED'
end,
-- substring(SYSSCHEDULES.name,1,30) AS [Name of the schedule],
'Job Frequency '=case
WHEN SYSSCHEDULES.freq_type = 1 THEN 'ONCE'
WHEN SYSSCHEDULES.freq_type = 4 THEN 'DAILY'
WHEN SYSSCHEDULES.freq_type = 8 THEN 'WEEKLY'
WHEN SYSSCHEDULES.freq_type = 16 THEN 'Monthly'
WHEN SYSSCHEDULES.freq_type = 32 THEN 'MONTHLY RELATIVE'
WHEN SYSSCHEDULES.freq_type = 32 THEN 'START AUTOMATICALLY WHEN SQL AGENT STARTS'
END,

'Days jobs run'=case
WHEN SYSSCHEDULES.[freq_interval] = 1 THEN ' SUNDAY'
WHEN SYSSCHEDULES.[freq_interval] = 2 THEN ' MONDAY'
WHEN SYSSCHEDULES.[freq_interval] = 3 THEN ' TUESDAY'
WHEN SYSSCHEDULES.[freq_interval] = 4 THEN ' WEDNESDAY'
WHEN SYSSCHEDULES.[freq_interval] = 5 THEN ' THURSDAY'
WHEN SYSSCHEDULES.[freq_interval] = 6 THEN ' FRIDAY'
WHEN SYSSCHEDULES.[freq_interval] = 7 THEN ' SATURDAY'
WHEN SYSSCHEDULES.[freq_interval] = 8 THEN ' DAILY'
WHEN SYSSCHEDULES.[freq_interval] = 9 THEN ' WEEKLY'
WHEN SYSSCHEDULES.[freq_interval] = 10 THEN 'WEEKEND'
WHEN SYSSCHEDULES.[freq_interval] = 62 THEN 'MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY'
WHEN SYSSCHEDULES.[freq_interval] = 64 THEN 'SATURDAY'
WHEN SYSSCHEDULES.[freq_interval] = 65 THEN 'SATURDAY, SUNDAY'
WHEN SYSSCHEDULES.[freq_interval] = 126 THEN 'MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY'
END,

'INTERVAL TYPE'=case
WHEN SYSSCHEDULES.freq_subday_type = 1 THEN 'At the specified time'
WHEN SYSSCHEDULES.freq_subday_type = 2 THEN 'Seconds'
WHEN SYSSCHEDULES.freq_subday_type = 4 THEN 'Minutes'
WHEN SYSSCHEDULES.freq_subday_type = 8 THEN 'Hours'
END,
cast(cast(SYSSCHEDULES.active_start_date as varchar(15)) as datetime) as StartDate,
cast(cast(SYSSCHEDULES.active_end_date as varchar(15)) as datetime) as EndDate,
Stuff(Stuff(right('000000'+Cast(SYSJOBSCHEDULES.ne xt_run_time as Varchar),6),3,0,':'),6,0,':') as Run_Time


FROM msdb..sysjobs SYSJOBS
INNER JOIN msdb..sysjobhistory SYSJOBHISTORY ON SYSJOBHISTORY.job_id = SYSJOBS.job_id
INNER JOIN msdb..sysJobschedules SYSJOBSCHEDULES ON SYSJOBSCHEDULES.job_id = SYSJOBS.job_id
INNER JOIN msdb..SysSchedules SYSSCHEDULES ON SYSSCHEDULES.Schedule_id = SYSJOBSCHEDULES.Schedule_id
INNER JOIN msdb..sysjobsteps SYSJOBSTEPS ON SYSJOBSTEPS.job_id = SYSJOBS.job_id
INNER JOIN msdb..syscategories SYSCATEGORIES ON SYSCATEGORIES.category_id = SYSJOBS.category_id



This work's fine on SQL 2005 Sp 2, however I would like to have it tell me at what intervals it run at (every 20 mins between 6:00 – 00:00 a, basically I would get this kind of info for a job


JOB NAME =DBA- Backup ***** logs
STEP NAME = Log backup for *****
CATEGORY = DBA Admin
ENABLED = Yes
JOB FREQUENCY = Weekly
ON WHAT DAYS = Mon, Tue, Wed, Thurs, Fri
INTERVAL TYPE = Every Hour
EXECUTION TIME = 8:00 – 19:00
DESCRIPTION = This job backs up the log for *******

I would be very gratefully, as I just can seem to do it.

Thanks

 

[SQL_Hell]

Hi there

Nice query

You want two additional columns from sysSchedules

active_start_time
active_end_time

This will give you the time interval that the job can execute
(i.e. 8:00 - 18:00)

 

[mec01]

Thank for your help, but managed to crack it. Also added Duration time Max, Min and avg



,substring(right(stuff(' ', 1, 1,'000000')+convert(VARCHAR(6),active_start_time), 6), 1, 2)+':'
+substring(right(stuff(' ', 1, 1,'000000')+convert(VARCHAR(6),active_start_time), 6),3 ,2)+':'
+substring(right(stuff(' ', 1, 1,'000000')+convert(VARCHAR(6),active_start_time), 6),5 ,2)
AS'JOB START TIME',
CASE freq_subday_type WHEN 1 THENNULL-- Ignore the end time if not a recurring job
ELSEsubstring(right(stuff(' ', 1, 1,'000000')+convert(VARCHAR(6), active_end_time), 6), 1, 2)+':'
+substring(right(stuff(' ', 1, 1,'000000')+convert(VARCHAR(6), active_end_time), 6),3 ,2)+':'
+substring(right(stuff(' ', 1, 1,'000000')+convert(VARCHAR(6), active_end_time), 6),5 ,2)
END
AS'JOB END TIME'

, SYSJOBS.description AS JobDescription
,MIN((SYSJOBHISTORY.[run_duration]/10000*3600 +(SYSJOBHISTORY.[run_duration]/100)%100*60 + SYSJOBHISTORY.[run_duration]%100 + 31 )/ 60)AS [Min Run Time]
,AVG((SYSJOBHISTORY.[run_duration]/10000*3600 +(SYSJOBHISTORY.[run_duration]/100)%100*60 + SYSJOBHISTORY.[run_duration]%100 + 31 )/ 60)AS [Avg Run Time]
,MAX((SYSJOBHISTORY.[run_duration]/10000*3600 +(SYSJOBHISTORY.[run_duration]/100)%100*60 + SYSJOBHISTORY.[run_duration]%100 + 31 )/ 60)AS [Max Run Time]


BUT YOU MUST USE GROUP BY

GROUPBY SYSJOBS.name, SYSJOBSTEPS.step_name, SYSCATEGORIES.name, SYSJOBS.date_created, SYSSCHEDULES.enabled,
SYSSCHEDULES.freq_type, SYSSCHEDULES.[freq_interval], SYSSCHEDULES.freq_subday_interval, SYSSCHEDULES.freq_subday_type,
SYSSCHEDULES.active_start_time, SYSSCHEDULES.active_end_time, SYSJOBS.description
ORDERBY JOB_NAME