Find Jobs scheduled to run during a specific time range

Hi Friends,Today I was going to raise a change which was related to SAN Migration for one of our production servers. The affected server will not be available for almost 3 hours. I prepared the plan and asked Mr. X to look into this & execute the change during the weekend. Mr.

 
 
 
 
 
3 Star
(4)
3,669 times
Add to favorites
Databases
11/6/2012
E-mail Twitter del.icio.us Digg Facebook
  • Jobs that run multiple times in the time range
    1 Posts | Last post August 11, 2017
    • This script however only shows the first next run time of the jobs. So it does not tell us if a job is run multiple times during the given time range, right?
  • if you don't want to create a function
    1 Posts | Last post July 04, 2017
    • Hello and thankx for your query.
      Since I don't want to create a function, I am using something similar:
      
      DECLARE @DateFrom as Datetime = '20170705 02:00'
      DECLARE @DateTo as Datetime = '20170705 05:00'
      
      ;WITH CTE AS(
      SELECT name,
      DateAdd(Second,next_run_time % 100,
      DateAdd(Minute,(next_run_time/100)%100,
      DateAdd(Hour, next_run_time/10000,
      DateAdd(day, (js.next_run_date%100) - 1, 
      DateAdd(month, ((js.next_run_date/100)%100) - 1, 
      DateAdd(Year, (js.next_run_date/10000)-1900, 0)))))) as dDate
      
      from msdb..sysjobschedules js inner join msdb..sysjobs j
      on j.job_id = js.job_id
      where enabled = 1
      )
      SELECT * FROM CTE 
      WHERE dDate BETWEEN @DateFrom AND @DateTo
      order by dDate
  • Errors
    2 Posts | Last post November 06, 2012
    • Hi there,
      I receive all of these errors using sql server 2005 sp4. 
      
      Msg 1001, Level 15, State 1, Procedure msdb_time_readable, Line 5
      Line 5: Length or precision specification 0 is invalid.
      Msg 178, Level 15, State 1, Procedure msdb_time_readable, Line 19
      A RETURN statement with a return value cannot be used in this context.
      Msg 139, Level 15, State 1, Procedure msdb_time_readable, Line 0
      Cannot assign a default value to a local variable.
      Msg 137, Level 15, State 2, Procedure msdb_time_readable, Line 22
      Must declare the scalar variable "@str".
      Msg 137, Level 15, State 2, Procedure msdb_time_readable, Line 23
      Must declare the scalar variable "@str".
      Msg 137, Level 15, State 2, Procedure msdb_time_readable, Line 24
      Must declare the scalar variable "@str".
      Msg 1001, Level 15, State 1, Procedure msdb_time_readable, Line 26
      Line 26: Length or precision specification 0 is invalid.
    • Hi Ben,
      this script is not compatible with SQL 2005 because the datatypes time, date were not available in sql 2005.
      Both these data types were introduced in SQL Server 2008, so my script will work on SQL Server 2008 and above.
  • Similar errors
    2 Posts | Last post November 06, 2012
    • Hey,
      I'm getting similar errors to Sunshine, see below:
      
      Msg 1001, Level 15, State 1, Procedure msdb_time_readable, Line 21
      Line 21: Length or precision specification 0 is invalid.
      Msg 178, Level 15, State 1, Procedure msdb_time_readable, Line 35
      A RETURN statement with a return value cannot be used in this context.
      Msg 139, Level 15, State 1, Procedure msdb_time_readable, Line 0
      Cannot assign a default value to a local variable.
      Msg 137, Level 15, State 2, Procedure msdb_time_readable, Line 38
      Must declare the scalar variable "@str".
      Msg 137, Level 15, State 2, Procedure msdb_time_readable, Line 39
      Must declare the scalar variable "@str".
      Msg 137, Level 15, State 2, Procedure msdb_time_readable, Line 40
      Must declare the scalar variable "@str".
      Msg 1001, Level 15, State 1, Procedure msdb_time_readable, Line 42
      Line 42: Length or precision specification 0 is invalid.
      Msg 4121, Level 16, State 1, Line 3
      Cannot find either column "dbo" or the user-defined function or aggregate "dbo.msdb_time_readable", or the name is ambiguous.
      
    • Hi Sunshine,
      this script is not compatible with SQL 2005 because the datatypes time, date were not available in sql 2005.
      Both these data types were introduced in SQL Server 2008, so my script will work on SQL Server 2008 and above.