none
Do you have a script to list SQL Jobs with Schedule summary?

    Question

  •  

    I'm looking for a clean summary of schudule like you see on property page.  Average duration would be greate too.

     

    Thanks.

    Wednesday, July 30, 2008 6:31 PM

Answers

  • It ain't pretty, but should get the job done.  Warning:  I have to post over several posts, since it is very long....

     

    --POST #1

    USE MSDB

    DECLARE

    @JOB_ID VARCHAR(200),

    @SCHED_ID VARCHAR(200),

    @FREQ_TYPE INT,

    @FREQ_INTERVAL INT,

    @FREQ_SUBDAY_TYPE INT,

    @FREQ_SUBDAY_INTERVAL INT,

    @FREQ_RELATIVE_INTERVAL INT,

    @FREQ_RECURRENCE_FACTOR INT,

    @ACTIVE_START_DATE INT,

    @SCHEDULE VARCHAR(1000),

    @SCHEDULE_DAY VARCHAR(200),

    @START_TIME VARCHAR(10),

    @END_TIME VARCHAR(10)

    CREATE TABLE #SCHEDULES

    (JOB_ID VARCHAR(200),

    SCHED_ID VARCHAR(200),

    JOB_NAME SYSNAME,

    [STATUS] INT,

    SCHEDULED INT NULL,

    schedule VARCHAR(1000) NULL,

    FREQ_TYPE INT NULL,

    FREQ_INTERVAL INT NULL,

    FREQ_SUBDAY_TYPE INT NULL,

    FREQ_SUBDAY_INTERVAL INT NULL,

    FREQ_RELATIVE_INTERVAL INT NULL,

    FREQ_RECURRENCE_FACTOR INT NULL,

    ACTIVE_START_DATE INT NULL,

    ACTIVE_END_DATE INT NULL,

    ACTIVE_START_TIME INT NULL,

    ACTIVE_END_TIME INT NULL,

    DATE_CREATED DATETIME NULL)

    INSERT INTO #SCHEDULES (

    job_id,

    sched_id ,

    job_name ,

    [status] ,

    Scheduled ,

    schedule ,

    freq_type,

    freq_interval,

    freq_subday_type,

    freq_subday_interval,

    freq_relative_interval,

    freq_recurrence_factor,

    active_start_date,

    active_end_date,

    active_start_time,

    active_end_time,

    date_created)

    SELECT

    j.job_id,

    sched.schedule_id,

    j.name ,

    j.enabled,

    sched.enabled,

    NULL,

    sched.freq_type,

    sched.freq_interval,

    sched.freq_subday_type,

    sched.freq_subday_interval,

    sched.freq_relative_interval,

    sched.freq_recurrence_factor,

    sched.active_start_date,

    sched.active_end_date,

    sched.active_start_time,

    sched.active_end_time,

    j.date_created

    FROM sysjobs j

    inner join

    sysjobschedules s

    ON j.job_id=s.job_id

    INNER JOIN dbo.sysschedules sched

    ON s.schedule_id = sched.schedule_id

     

    Wednesday, July 30, 2008 11:18 PM

All replies

  • All of that data is held in the msdb database, sysjobs, sysjobhistory, sysjobschedules tables.  Your request is very non-descript, but you can easily query those tables to see what data exists.  If you provide a sample of what you want the output to be, or explain how to get to the screen that you are referencing as the property page, we can probably steer your request better.

     

    Wednesday, July 30, 2008 8:22 PM
    Moderator
  • I've been look at those tables but if you look at the properties of a sql job you will see a summary description of the schedule like Occurs every week on Monday, Tuesday, Wednesday, Thursday, Friday every 1 hour(s) between 5:55:00 AM and 6:55:00 PM. Schedule will be used starting on 10/29/2007.

     

    Wednesday, July 30, 2008 8:38 PM
  • Have you looked at sp_help_job in msdb?  You can provide it the Job_id, and it will output the job information.  Keep in mind that SQL is only going to output columns and rows.  The format is all done in the Application, not in SQL.

     

    exec msdb..sp_help_job 'job_uniqueidentifier'

    Wednesday, July 30, 2008 9:44 PM
    Moderator
  • Yes, that is what I'm asking.  Has anyone done this already?

    Wednesday, July 30, 2008 9:50 PM
  • I do apologize, but I am not following what you want, or what you are after?  If it is how to write a UI to display the information from sp_help_job or queries from the tables then this is the wrong forum for this post.  All of the information you need is in the sp_help_job procedure, and or the tables, it is just a matter of defining how you want it to output, which you haven't done in this post yet.

     

    The more detail you provide the better we can answer your question.  So far you have been pretty vague with what you are actually trying to do.

    Wednesday, July 30, 2008 9:57 PM
    Moderator
  • This is for SQL 2005, right?  I have one for 2000, but needs to be made compatible for 05...will try to post later.

     

    Wednesday, July 30, 2008 10:25 PM
  • It ain't pretty, but should get the job done.  Warning:  I have to post over several posts, since it is very long....

     

    --POST #1

    USE MSDB

    DECLARE

    @JOB_ID VARCHAR(200),

    @SCHED_ID VARCHAR(200),

    @FREQ_TYPE INT,

    @FREQ_INTERVAL INT,

    @FREQ_SUBDAY_TYPE INT,

    @FREQ_SUBDAY_INTERVAL INT,

    @FREQ_RELATIVE_INTERVAL INT,

    @FREQ_RECURRENCE_FACTOR INT,

    @ACTIVE_START_DATE INT,

    @SCHEDULE VARCHAR(1000),

    @SCHEDULE_DAY VARCHAR(200),

    @START_TIME VARCHAR(10),

    @END_TIME VARCHAR(10)

    CREATE TABLE #SCHEDULES

    (JOB_ID VARCHAR(200),

    SCHED_ID VARCHAR(200),

    JOB_NAME SYSNAME,

    [STATUS] INT,

    SCHEDULED INT NULL,

    schedule VARCHAR(1000) NULL,

    FREQ_TYPE INT NULL,

    FREQ_INTERVAL INT NULL,

    FREQ_SUBDAY_TYPE INT NULL,

    FREQ_SUBDAY_INTERVAL INT NULL,

    FREQ_RELATIVE_INTERVAL INT NULL,

    FREQ_RECURRENCE_FACTOR INT NULL,

    ACTIVE_START_DATE INT NULL,

    ACTIVE_END_DATE INT NULL,

    ACTIVE_START_TIME INT NULL,

    ACTIVE_END_TIME INT NULL,

    DATE_CREATED DATETIME NULL)

    INSERT INTO #SCHEDULES (

    job_id,

    sched_id ,

    job_name ,

    [status] ,

    Scheduled ,

    schedule ,

    freq_type,

    freq_interval,

    freq_subday_type,

    freq_subday_interval,

    freq_relative_interval,

    freq_recurrence_factor,

    active_start_date,

    active_end_date,

    active_start_time,

    active_end_time,

    date_created)

    SELECT

    j.job_id,

    sched.schedule_id,

    j.name ,

    j.enabled,

    sched.enabled,

    NULL,

    sched.freq_type,

    sched.freq_interval,

    sched.freq_subday_type,

    sched.freq_subday_interval,

    sched.freq_relative_interval,

    sched.freq_recurrence_factor,

    sched.active_start_date,

    sched.active_end_date,

    sched.active_start_time,

    sched.active_end_time,

    j.date_created

    FROM sysjobs j

    inner join

    sysjobschedules s

    ON j.job_id=s.job_id

    INNER JOIN dbo.sysschedules sched

    ON s.schedule_id = sched.schedule_id

     

    Wednesday, July 30, 2008 11:18 PM
  • --POST #2

     

    WHILE 1=1

    BEGIN

    SET @SCHEDULE = ''

    IF (SELECT COUNT(*) FROM #SCHEDULES WHERE scheduled=1 and schedule is null) = 0

    BREAK

    ELSE

    BEGIN

    SELECT

    @job_id=job_id,

    @sched_id=sched_id,

    @freq_type=freq_type,

    @Freq_Interval=freq_interval,

    @freq_subday_type=freq_subday_type,

    @freq_subday_interval=freq_subday_interval,

    @freq_relative_interval=freq_relative_interval,

    @freq_recurrence_factor=freq_recurrence_factor,

    @active_start_date = active_start_date,

    @start_time =

    CASE

    WHEN

    LEFT(active_start_time, 2) IN (22, 23) AND len(active_start_time) = 6

    THEN

    convert(varchar(2), left(active_start_time, 2) - 12)

    + ':' + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ' P.M'

    WHEN

    left(active_start_time, 2) = (12) AND len(active_start_time) = 6

    THEN

    cast(LEFT(active_start_time,2) as char(2))

    + ':' + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ' P.M.'

    WHEN

    left(active_start_time, 2) BETWEEN 13 AND 24 AND len(active_start_time) = 6

    THEN

    convert(varchar(2), left(active_start_time, 2) - 12)

    + ':' + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ' P.M.'

    WHEN

    left(active_start_time, 2) IN (10, 11) AND len(active_start_time) = 6

    THEN

    cast(LEFT(active_start_time,2) as char(2))

    + ':' + SUBSTRING(CAST(active_start_time AS CHAR),3, 2) + ' A.M.'

    WHEN

    active_start_time = 0

    THEN

    '12:00 A.M.'

    WHEN

    LEN(active_start_time) = 4

    THEN

    '12:' + convert(varchar(2), left(active_start_time, 2) ) + ' A.M.'

    WHEN

    LEN(active_start_time) = 3

    THEN

    '12:0' + convert(varchar(2), left(active_start_time, 1) ) + ' A.M.'

    WHEN

    LEN(active_start_time) = 2

    THEN

    '12:00:' + convert(varchar(2), left(active_start_time, 2) ) + ' A.M.'

    WHEN

    LEN(active_start_time) = 1

    THEN

    '12:00:0' + convert(varchar(2), left(active_start_time, 2) ) + ' A.M.'

    ELSE

    cast(LEFT(active_start_time,1) as char(1))

    + ':' + SUBSTRING(CAST(active_start_time AS CHAR),2, 2) + ' A.M.'

    END,

    @END_TIME= CASE

    WHEN

    left(active_end_time, 2) IN (22, 23) AND len(active_end_time) = 6

    THEN

    convert(varchar(2), left(active_end_time, 2) - 12)

    + ':' + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ' P.M'

    WHEN

    left(active_end_time, 2) = (12) AND len(active_end_time) = 6

    THEN

    cast(LEFT(active_end_time,2) as char(2))

    + ':' + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ' P.M.'

    WHEN

    left(active_end_time, 2) BETWEEN 13 AND 24 AND len(active_end_time) = 6

    THEN

    convert(varchar(2), left(active_end_time, 2) - 12)

    + ':' + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ' P.M.'

    WHEN

    left(active_end_time, 2) IN (10, 11) AND len(active_end_time) = 6

    THEN

    cast(LEFT(active_end_time,2) as char(2))

    + ':' + SUBSTRING(CAST(active_end_time AS CHAR),3, 2) + ' A.M.'

    WHEN

    active_end_time = 0

    THEN

    '12:00 A.M.'

    WHEN

    LEN(active_end_time) = 4

    THEN

    '12:' + convert(varchar(2), left(active_end_time, 2) ) + ' A.M.'

    WHEN

    LEN(active_end_time) = 3

    THEN

    '12:0' + convert(varchar(2), left(active_end_time, 1) ) + ' A.M.'

    WHEN

    LEN(active_end_time) = 2

    THEN

    '12:00:' + convert(varchar(2), left(active_end_time, 2) ) + ' A.M.'

    WHEN

    LEN(active_end_time) = 1

    THEN

    '12:00:0' + convert(varchar(2), left(active_end_time, 2) ) + ' A.M.'

    ELSE

    cast(LEFT(active_end_time,1) as char(1))

    + ':' + SUBSTRING(CAST(active_end_time AS CHAR),2, 2) + ' A.M.'

    END

    FROM #SCHEDULES

    WHERE schedule is null

    AND scheduled=1

     

    Wednesday, July 30, 2008 11:18 PM
  • --POST #3

     

    IF EXISTS(SELECT @freq_type WHERE @freq_type in (1,64))

    BEGIN

    SELECT @SCHEDULE = CASE @freq_type

    WHEN 1 THEN 'Occurs Once, On '+cast(@active_start_date as varchar(8))+', At '+@start_time

    WHEN 64 THEN 'Occurs When SQL Server Agent Starts'

    END

    END

    ELSE

    BEGIN

    IF @freq_type=4

    BEGIN

    SELECT @SCHEDULE = 'Occurs Every '+cast(@freq_interval as varchar(10))+' Day(s)'

    END

    IF @freq_type=8

    BEGIN

    SELECT @SCHEDULE = 'Occurs Every '+cast(@freq_recurrence_factor as varchar(3))+' Week(s)'

    SELECT @schedule_day=''

    IF (SELECT (convert(int,(@freq_interval/1)) % 2)) = 1

    select @schedule_day = @schedule_day+'Sun'

    IF (SELECT (convert(int,(@freq_interval/2)) % 2)) = 1

    select @schedule_day = @schedule_day+'Mon'

    IF (SELECT (convert(int,(@freq_interval/4)) % 2)) = 1

    select @schedule_day = @schedule_day+'Tue'

    IF (SELECT (convert(int,(@freq_interval/8)) % 2)) = 1

    select @schedule_day = @schedule_day+'Wed'

    IF (SELECT (convert(int,(@freq_interval/16)) % 2)) = 1

    select @schedule_day = @schedule_day+'Thu'

    IF (SELECT (convert(int,(@freq_interval/32)) % 2)) = 1

    select @schedule_day = @schedule_day+'Fri'

    IF (SELECT (convert(int,(@freq_interval/64)) % 2)) = 1

    select @schedule_day = @schedule_day+'Sat'

    SELECT @SCHEDULE = @SCHEDULE+', On '+@schedule_day

    END

    IF @freq_type=16

    BEGIN

    SELECT @SCHEDULE = 'Occurs Every '+cast(@freq_recurrence_factor as varchar(3))+' Month(s) on Day '+cast(@freq_interval as varchar(3))+' of that Month'

    END

    IF @freq_type=32

    BEGIN

    SELECT @SCHEDULE = CASE @freq_relative_interval

    WHEN 1 THEN 'First'

    WHEN 2 THEN 'Second'

    WHEN 4 THEN 'Third'

    WHEN 8 THEN 'Fourth'

    WHEN 16 THEN 'Last'

    ELSE 'Not Applicable'

    END

    SELECT @SCHEDULE =

    CASE @freq_interval

    WHEN 1 THEN 'Occurs Every '+@SCHEDULE+' Sunday of the Month'

    WHEN 2 THEN 'Occurs Every '+@SCHEDULE+' Monday of the Month'

    WHEN 3 THEN 'Occurs Every '+@SCHEDULE+' Tueday of the Month'

    WHEN 4 THEN 'Occurs Every '+@SCHEDULE+' Wednesday of the Month'

    WHEN 5 THEN 'Occurs Every '+@SCHEDULE+' Thursday of the Month'

    WHEN 6 THEN 'Occurs Every '+@SCHEDULE+' Friday of the Month'

    WHEN 7 THEN 'Occurs Every '+@SCHEDULE+' Saturday of the Month'

    WHEN 8 THEN 'Occurs Every '+@SCHEDULE+' Day of the Month'

    WHEN 9 THEN 'Occurs Every '+@SCHEDULE+' Weekday of the Month'

    WHEN 10 THEN 'Occurs Every '+@SCHEDULE+' Weekend Day of the Month'

    END

    END

    SELECT @SCHEDULE =

    CASE @freq_subday_type

    WHEN 1 THEN @SCHEDULE+', At '+@start_time

    WHEN 2 THEN @SCHEDULE+', every '+cast(@freq_subday_interval as varchar(3))+' Second(s) Between '+@start_time+' and '+@END_TIME

    WHEN 4 THEN @SCHEDULE+', every '+cast(@freq_subday_interval as varchar(3))+' Minute(s) Between '+@start_time+' and '+@END_TIME

    WHEN 8 THEN @SCHEDULE+', every '+cast(@freq_subday_interval as varchar(3))+' Hour(s) Between '+@start_time+' and '+@END_TIME

    END

    END

    END

    UPDATE #SCHEDULES

    SET schedule=@SCHEDULE

    WHERE job_id=@job_id

    AND sched_id=@sched_Id

    END

    SELECT job_name ,

    [status] = CASE STATUS

    WHEN 1 THEN 'ENABLED'

    WHEN 0 THEN 'DISABLED'

    ELSE ' '

    END,

    scheduled= case scheduled

    when 1 then 'Yes'

    when 0 then 'No'

    else ' '

    end,

    schedule as 'Frequency' ,

    convert(datetime, convert(varchar,active_start_date, 101)) AS schedule_start_date,

    convert(datetime, convert(varchar,active_end_date, 101)) AS schedule_end_date,

    date_created

    FROM #schedules

    WHERE scheduled=1

    ORDER BY job_name

    DROP TABLE #schedules

     

     

     

     

    Wednesday, July 30, 2008 11:19 PM
  • Make sure to create POST 1, 2, and 3 as one entire query.  The column called, schedule_start_date, is the same one used in: Schedule will be used starting on such and such date.  It just isn't concatenated with the rest of the schedule info.  Let me know if you need it to be.  The query can also be created as a proc.  Should give you most of what you are looking for, or at least an idea.  Oh yeah, and here is a query to get the duration of job steps:

     

    USE [msdb]

    select s.step_name,

    --s.command,

    case when last_run_outcome = 1

    then 'SUCCESS'

    ELSE 'FAIL'

    END AS outcome,

    case when len(last_run_duration) = 1

    then '0:0' + convert(varchar, last_run_duration)

    when len(last_run_duration) = 2

    then '0:' + convert(varchar, last_run_duration)

    when len(last_run_duration) = 3

    then left(convert(varchar, last_run_duration),1) +

    ':' + right(convert(varchar, last_run_duration), 2)

    when len(last_run_duration) = 4

    then left(convert(varchar, last_run_duration),2) +

    ':' + right(convert(varchar, last_run_duration), 2)

    when len(last_run_duration) = 5

    then left(convert(varchar, last_run_duration),3) +

    ':' + right(convert(varchar, last_run_duration), 2)

    end as duration

    from dbo.sysjobsteps s

    inner join dbo.sysjobs j

    on j.job_id = s.job_id

    where name = 'YourJobName'

    order by j.name,last_run_duration desc

     

     

     

    Wednesday, July 30, 2008 11:21 PM
  • Thanks Sal,

     

    Another tool to add to the kit.

    Thursday, July 31, 2008 1:20 AM
    Moderator
  •  

    You're welcome. Smile
    Thursday, July 31, 2008 6:21 PM
  • Thank you very much.  This is awesome.  I hate reinventing the wheel and it is nice when people share.

     

    here are some things I changed.

    I added:

    WHERE j.enabled = 1 and sched.enabled = 1

    I added sched.name to distiguish jobs that had mutiple schedules in the output.

    I changed  dbo.sysschedules to dbo.sysjobschedules

    Friday, August 01, 2008 7:35 PM
  • Good info, Can you post the complete script for sql 2000 also, thx.
    Tuesday, March 09, 2010 5:21 PM
  • Below script can help you with the duration & jobs list with Enabled/disabled status too.

     

    SET  NOCOUNT ON
     
    DECLARE @MaxLength   INT
    SET @MaxLength   = 50

    DECLARE @xp_results TABLE (
                           job_id UNIQUEIDENTIFIER NOT NULL,
                           last_run_date NVARCHAR (20) NOT NULL,
                           last_run_time NVARCHAR (20) NOT NULL,
                           next_run_date NVARCHAR (20) NOT NULL,
                           next_run_time NVARCHAR (20) NOT NULL,
                           next_run_schedule_id INT NOT NULL,
                           requested_to_run INT NOT NULL,
                           request_source INT NOT NULL,
                           request_source_id sysname
                                 COLLATE database_default NULL,
                           running INT NOT NULL,
                           current_step INT NOT NULL,
                           current_retry_attempt INT NOT NULL,
                           job_state INT NOT NULL
                        )
     
    DECLARE @job_owner   sysname
     
    DECLARE @is_sysadmin   INT
    SET @is_sysadmin   = isnull (IS_SRVROLEMEMBER ('sysadmin'), 0)
    SET @job_owner   = SUSER_SNAME ()
     
    INSERT INTO @xp_results
       EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
     
    UPDATE @xp_results
       SET last_run_time    = RIGHT ('000000' + last_run_time, 6),
           next_run_time    = RIGHT ('000000' + next_run_time, 6)
     
    SELECT j.name AS JobName,
           CASE j.enabled WHEN 1 THEN 'Enabled' ELSE 'DISABLED' END JobEnabled,
           CASE x.running
              WHEN 1
              THEN
                 'Running'
              ELSE
                 CASE h.run_status
                    WHEN 2 THEN 'Inactive'
                    WHEN 4 THEN 'Inactive'
                    ELSE 'Completed'
                 END
           END
              AS CurrentStatus,      
           CASE
              WHEN x.last_run_date > 0
              THEN
                 CONVERT (DATETIME,
                            SUBSTRING (x.last_run_date, 1, 4)
                          + '-'
                          + SUBSTRING (x.last_run_date, 5, 2)
                          + '-'
                          + SUBSTRING (x.last_run_date, 7, 2)
                          + ' '
                          + SUBSTRING (x.last_run_time, 1, 2)
                          + ':'
                          + SUBSTRING (x.last_run_time, 3, 2)
                          + ':'
                          + SUBSTRING (x.last_run_time, 5, 2)
                          + '.000',
                          121
                 )
              ELSE
                 NULL
           END
              AS LastRunTime,
           CASE h.run_status
              WHEN 0 THEN 'Fail'
              WHEN 1 THEN 'Success'
              WHEN 2 THEN 'Retry'
              WHEN 3 THEN 'Cancel'
              WHEN 4 THEN 'In progress'
           END
              AS LastRunOutcome,
             
              --Change run duration into something you can recognize (hh:mm:ss)
     LEFT(RIGHT('000000' + CAST(h.run_duration AS VARCHAR(10)),6),2) + ':' +
     SUBSTRING(RIGHT('000000' + CAST(h.run_duration AS VARCHAR(10)),6),3,2) + ':' +
     RIGHT(RIGHT('000000' + CAST(h.run_duration AS VARCHAR(10)),6),2) LastRunDuration

      FROM @xp_results x
           LEFT JOIN
                    msdb.dbo.sysjobs j
                 ON x.job_id = j.job_id
           LEFT OUTER JOIN
                 msdb.dbo.syscategories c
              ON j.category_id = c.category_id
           LEFT OUTER JOIN
              msdb.dbo.sysjobhistory h
           ON     x.job_id = h.job_id
              AND x.last_run_date = h.run_date
              AND x.last_run_time = h.run_time
              AND h.step_id = 0
          ORDER BY j.Enabled,h.run_time,j.Name

    Wednesday, June 08, 2011 10:14 PM