SQL Agent Job Queries to get schedule, frequency, duration (min, max, avg)

• RCC-JAXDBA

  Mr or Mrs. 500

  Points: 557

  More actions

  June 19, 2008 at 12:10 pm

  #187378

  I have to write a query to generate the job name, min duration, max duration, avg duration, schedule (Daily, Weekly, etc), and frequency (every hour, once a day, etc)

  I have the query already to get me the job name, min duration, max duration, avg duration. I cannot find a system table that holds the schedule and frequency information.

  sysjobschedules just shows you the next run time and run date

  Does anyone have any insight that might help me with this

  Here is what I have so far

  USE msdb

  GO

  select job_id, name into #jobs from sysjobs where enabled = 1

  GO

  select job_id,

  run_date,

  run_time,

  ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) as 'run_duration'

  into #jobhistory from sysjobhistory

  where step_id = 0

  select a.name as 'JobName',

  max(b.run_duration) as 'MaxDurationMinutes',

  min(b.run_duration) as 'MinDurationMinutes',

  avg(b.run_duration) as 'AvgDurationMinutes'

  from #jobs a, #jobhistory b where a.job_id = b.job_id group by a.name

  order by a.name

  drop table #jobs

  drop table #jobhistory

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  June 19, 2008 at 2:48 pm

  #831429

  Use msdb.dbo.sysschedules.

  Greg