server reboots and Job timings

• DB King

  SSC Eights!

  Points: 806

  More actions

  September 29, 2006 at 3:39 am

  #97733

  hi

  I am trying to understand the best time to bring down a server for maintenance. I have a number of SQL 2000 and SQL 2005 servers which have many jobs configured to execute at numerous times during the day and night.

  I would like to ascertain when I can safely reboot a server without disturbing a job. I would like to do this using a single query which will give me a list of offending jobs given a time period. Anyone know how to do this ?? with MSDB I suppose ?

  regards

• EugeneZ-162636

  Default port

  Points: 1448

  More actions

  September 29, 2006 at 5:37 pm

  #663483

  yes, msdb:

   

  start collect and analyze data from queries:

   

  select

  j.name,j.enabled,

  sh

  .active_start_date,

  sh

  .active_start_time,

  sh

  .active_end_time

  from msdb.dbo.sysjobs j

  inner

  join

  msdb

  .dbo.sysjobschedules sh

  on

  j

  .job_id=sh.job_id

   

  select

  j.name,j.enabled,

  h

  .run_date,

  h

  .run_time,

  h

  .run_duration

  from msdb.dbo.sysjobs j

  inner

  join

  msdb

  .dbo.sysjobhistory h

  on

  j

  .job_id=h.job_id