Get job list

  • Need a query which gets me the list of jobs which is scheduled to run on a particular day and its scheduled run time. I need the schedule even if the job is disabled, but schedule enabled.

    I dont want to create a function. A query without creation of a stored procedure or function would be helpful.

  • In the simplest form, with no filters:

    SELECTsj.[name],

    sjs.next_run_date, sjs.next_run_time

    FROM MSDB..sysjobs sj

    JOIN MSDB..sysjobschedules sjs ON sj.job_id = sjs.job_id

    You can use MSDB..sysjobs, MSDB..sysjobschedules, MSDB..sysjobhistory, MSDB..sysjobservers, MSDB..sysjobactivity to JOIN and filter your data using WHERE clauses, and order the data.

    You can use the 'enabled' column in sysschedules too if you want to filter on schedule enabled and ignore the 'enabled' column in sysjobs.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • Query looks great. But, it missed out one detail. If the job is scheduled to run daily, then it gives the next run only and does not give the day after tomorrow's run date/time.

    If job runs daily, and i run the query on wednesday, it gives thursday date/time, but not fri,sat,sun dates. If i want to see what jobs run on sunday, how to include that in this (or any other) query.

  • Ah, I see. msdb..sysschedules will be your friend then, it defines freq_interval, freq_type, etc. and you should be able to join on schedule_id with msdb..sysjobschedules as a filter.

    Unfortunately I don't have time to put together the full query for you, but it's an interesting thing to do that I'll try and get around to - always nice to have something new for my code dump every day!

    I would build this as an SP though - you can then feed in a date or range to get filtered info out.

    If you wanted a quick view on jobs and schedules that you can eyeball, have you considered using Red Gate Backup to view and order the jobs?

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • Oh oh... then i would be waiting for the script with bated breath 🙁

  • In the meantime anyone out there who can help with the qury

  • balasach82 (10/30/2012)


    In the meantime anyone out there who can help with the qury

    deifinetely but why dont you give a try and post what exactly are you expecting in terms of column details..

    Regards
    Durai Nagarajan

  • check if this helps

    exec msdb.dbo.sp_help_job

    Regards
    Durai Nagarajan

  • PMed you with rough but tested script.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • derek.colley (10/30/2012)


    PMed you with rough but tested script.

    why not put it on forum for others to see.

    Regards
    Durai Nagarajan

  • balasach82 (10/30/2012)


    Need a query which gets me the list of jobs which is scheduled to run on a particular day and its scheduled run time. I need the schedule even if the job is disabled, but schedule enabled.

    I dont want to create a function. A query without creation of a stored procedure or function would be helpful.

    Below will help you, I use this and have it in my script repository... no need of creating function or anything like that ... you can create an SP as well :

    ---------------------------------------------------------------------------------------------------

    -- Date Created: September 21, 2006

    -- Author: William McEvoy

    --

    -- Description: This procedure produces a report that details the schedule information for all

    -- scheduled jobs on the server.

    --

    ---------------------------------------------------------------------------------------------------

    set nocount on

    select 'Server' = left(@@ServerName,20),

    'JobName' = left(S.name,30),

    'ScheduleName' = left(ss.name,25),

    'Enabled' = CASE (S.enabled)

    WHEN 0 THEN 'No'

    WHEN 1 THEN 'Yes'

    ELSE '??'

    END,

    'Frequency' = CASE(ss.freq_type)

    WHEN 1 THEN 'Once'

    WHEN 4 THEN 'Daily'

    WHEN 8 THEN (case when (ss.freq_recurrence_factor > 1) then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks' else 'Weekly' end)

    WHEN 16 THEN (case when (ss.freq_recurrence_factor > 1) then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)

    WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE

    WHEN 64 THEN 'SQL Startup'

    WHEN 128 THEN 'SQL Idle'

    ELSE '??'

    END,

    'Interval' = CASE

    WHEN (freq_type = 1) then 'One time only'

    WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'

    WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'

    WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7

    from (select ss.schedule_id,

    freq_interval,

    'D1' = CASE WHEN (freq_interval & 1 <> 0) then 'Sun ' ELSE '' END,

    'D2' = CASE WHEN (freq_interval & 2 <> 0) then 'Mon ' ELSE '' END,

    'D3' = CASE WHEN (freq_interval & 4 <> 0) then 'Tue ' ELSE '' END,

    'D4' = CASE WHEN (freq_interval & 8 <> 0) then 'Wed ' ELSE '' END,

    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu ' ELSE '' END,

    'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri ' ELSE '' END,

    'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat ' ELSE '' END

    from msdb..sysschedules ss

    where freq_type = 8

    ) as F

    where schedule_id = sj.schedule_id

    )

    WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)

    WHEN (freq_type = 32) then (select freq_rel + WDAY

    from (select ss.schedule_id,

    'freq_rel' = 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 '??'

    END,

    'WDAY' = CASE (freq_interval)

    WHEN 1 then ' Sun'

    WHEN 2 then ' Mon'

    WHEN 3 then ' Tue'

    WHEN 4 then ' Wed'

    WHEN 5 then ' Thu'

    WHEN 6 then ' Fri'

    WHEN 7 then ' Sat'

    WHEN 8 then ' Day'

    WHEN 9 then ' Weekday'

    WHEN 10 then ' Weekend'

    ELSE '??'

    END

    from msdb..sysschedules ss

    where ss.freq_type = 32

    ) as WS

    where WS.schedule_id =ss.schedule_id

    )

    END,

    'Time' = CASE (freq_subday_type)

    WHEN 1 then left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)

    WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'

    WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'

    WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'

    ELSE '??'

    END,

    'Next Run Time' = CASE SJ.next_run_date

    WHEN 0 THEN cast('n/a' as char(10))

    ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120) + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)

    END

    from msdb.dbo.sysjobschedules SJ

    join msdb.dbo.sysjobs S on S.job_id = SJ.job_id

    join msdb.dbo.sysschedules SS on ss.schedule_id = sj.schedule_id

    order by S.name

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply