List of scheduled sql jobs

  • Hi,

    Is there any query which can provide me with the list of scheduled job names, run time and frequently of running.

    Thanks for your replies.

    Cheers

    A.

  • You could write a query that joins msdb.dbo.sysjobs and msdb.dbo.sysjobschedules or run sp_help_job and sp_help_jobschedule.

    Greg

  • I don't have a code handy for SQL 2000

    but this one works in 2005 & 2008

    --Author: Saleem Hakani (Microsoft Corporation)

    --Email: Saleem@sqlcommunity.com

    --Website: http://www.sqlcommunity.com

    --Date: June 3rd 2008

    --Purpose: Provide SQL Server Agent related job information

    SELECT DISTINCT substring(a.name,1,100) AS [Job Name],

    'Enabled'=case

    WHEN a.enabled = 0 THEN 'No'

    WHEN a.enabled = 1 THEN 'Yes'

    end,

    substring(b.name,1,30) AS [Name of the schedule],

    'Frequency of the schedule execution'=case

    WHEN b.freq_type = 1 THEN 'Once'

    WHEN b.freq_type = 4 THEN 'Daily'

    WHEN b.freq_type = 8 THEN 'Weekly'

    WHEN b.freq_type = 16 THEN 'Monthly'

    WHEN b.freq_type = 32 THEN 'Monthly relative'

    WHEN b.freq_type = 32 THEN 'Execute when SQL Server Agent starts'

    END,

    'Units for the freq_subday_interval'=case

    WHEN b.freq_subday_type = 1 THEN 'At the specified time'

    WHEN b.freq_subday_type = 2 THEN 'Seconds'

    WHEN b.freq_subday_type = 4 THEN 'Minutes'

    WHEN b.freq_subday_type = 8 THEN 'Hours'

    END,

    cast(cast(b.active_start_date as varchar(15)) as datetime) as active_start_date,

    cast(cast(b.active_end_date as varchar(15)) as datetime) as active_end_date,

    cast(cast(c.next_run_date as varchar(15)) as datetime) as next_run_date,

    Stuff(Stuff(right('000000'+Cast(c.next_run_time as Varchar),6),3,0,':'),6,0,':') as Run_Time,

    b.date_created

    FROM msdb..sysjobhistory d

    INNER JOIN msdb..sysjobs a ON a.job_id = d.job_id

    INNER JOIN msdb..sysJobschedules c ON a.job_id = c.job_id

    INNER JOIN msdb..SysSchedules b on b.Schedule_id=c.Schedule_id

    GO

    Output

    Job NameEnabledName of the scheduleFrequency of the schedule executionUnits for the freq_subday_intervalactive_start_dateactive_end_datenext_run_dateRun_Timedate_created

    syspolicy_purge_historyYessyspolicy_purge_history_scheduDailyAt the specified time2008-01-01 00:00:00.0009999-12-31 00:00:00.0002008-11-19 00:00:00.00002:00:002008-10-20 15:08:49.377

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • other than that you also have

    msdb..sp_help_job

    (and also sp_help_job_schedule 'myjobname')

    MVDBA

  • Hi All,

    Thanks for your replies. I got the idea.

    Cheers

    A.

  • Jerry Hung (11/18/2008)


    I don't have a code handy for SQL 2000

    but this one works in 2005 & 2008

    (snipped)

    Output

    Thanks for this answer, with some mods, this helped with some detail today with an automated management question.

  • Here it is with dates formatted and ordered by freq and next run date & time. This works also in SQL Server 2012. (Darn, when I edited it, it lost all formatting. You'll have to reformat using either SQL Server 2012 Tools or Poor Man's T-SQL Formatter add-in for Notepad++)

    -- Henry Stinson, DBA

    --Purpose: Provide SQL Server Agent related job information

    SELECT DISTINCT substring(a.name,1,100) AS [Job Name],

    'Enabled'=case

    WHEN a.enabled = 0 THEN 'No'

    WHEN a.enabled = 1 THEN 'Yes'

    end,

    substring(b.name,1,30) AS [Name of the schedule],

    'Frequency of the schedule execution'=case

    WHEN b.freq_type = 1 THEN 'Once'

    WHEN b.freq_type = 4 THEN 'Daily'

    WHEN b.freq_type = 8 THEN 'Weekly'

    WHEN b.freq_type = 16 THEN 'Monthly'

    WHEN b.freq_type = 32 THEN 'Monthly relative'

    WHEN b.freq_type = 32 THEN 'Execute when SQL Server Agent starts'

    END,

    'Units for the freq_subday_interval'=case

    WHEN b.freq_subday_type = 1 THEN 'At the specified time'

    WHEN b.freq_subday_type = 2 THEN 'Seconds'

    WHEN b.freq_subday_type = 4 THEN 'Minutes'

    WHEN b.freq_subday_type = 8 THEN 'Hours'

    END,

    SUBSTRING(CAST(b.active_start_date AS VARCHAR(8)), 5, 2) + '-' + SUBSTRING(CAST(b.active_start_date AS VARCHAR(8)), 7, 2) + '-' + SUBSTRING(CAST(b.active_start_date AS VARCHAR(8)), 1, 4) As ActiveStartDate,

    --b.active_end_date,

    SUBSTRING(CAST(c.next_run_date AS VARCHAR(8)), 5, 2) + '-' + SUBSTRING(CAST(c.next_run_date AS VARCHAR(8)), 7, 2) + '-' + SUBSTRING(CAST(c.next_run_date AS VARCHAR(8)), 1, 4) As NextRunDate,

    --cast(cast(b.active_start_date as varchar(15)) as datetime) as active_start_date,

    --cast(cast(b.active_end_date as varchar(15)) as datetime) as active_end_date,

    --cast(cast(c.next_run_date as varchar(15)) as datetime) as next_run_date,

    Stuff(Stuff(right('000000'+Cast(c.next_run_time as Varchar),6),3,0,':'),6,0,':') as Run_Time,

    b.date_created

    FROM msdb..sysjobhistory d

    INNER JOIN msdb..sysjobs a

    ON a.job_id = d.job_id

    INNER JOIN msdb..sysJobschedules c

    ON a.job_id = c.job_id

    INNER JOIN msdb..SysSchedules b

    ON b.Schedule_id=c.Schedule_id

    ORDER BY [Frequency of the schedule execution],

    [NextRunDate],

    [Run_Time];

    GO

  • I realize this is a very old thread but thought I'd throw this out there in case someone else has experienced it.

    This code is almost exactly what I am looking for!  Thank you!  Unfortunately it returns, on one of my servers, 84 rows but when I count them manually, there are 113 jobs on that server.  I'm still digging through which ones are missing but I'm sure it has to do with the inner joins narrowing my search down.  
    Has anyone tweaked this to work to show ALL the jobs on a server?  

    Looks like someone already answered my question.  Thanks!!  😀

    Thanks, Henry!!

  • Yes, it'll only return jobs that are scheduled.  Change the INNER JOIN between sysjobs and sysjobschedules to a LEFT JOIN to see all jobs.

    John

  • the first table should be sysjobs, as some jobs may exist but have NEVER been executed,and thus have no history.
    also, the joins should be LEFT JOINs, since jobs could exist, but have no schedules.
    another issue, which is more of a "I expected that", is  i have a couple jobs with multiple schedules, so they appear twice.

    FROM msdb..sysjobs a
     LEFT JOIN msdb..sysjobhistory d
       ON a.job_id = d.job_id
     LEFT JOIN msdb..sysJobschedules c
       ON a.job_id = c.job_id
     LEFT JOIN msdb..SysSchedules b
       ON b.Schedule_id=c.Schedule_id
    ORDER BY [Frequency of the schedule execution],
       [NextRunDate],
       [Run_Time];

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • John Waclawski - Friday, July 14, 2017 6:52 AM

    I realize this is a very old thread but thought I'd throw this out there in case someone else has experienced it.

    This code is almost exactly what I am looking for!  Thank you!  Unfortunately it returns, on one of my servers, 84 rows but when I count them manually, there are 113 jobs on that server.  I'm still digging through which ones are missing but I'm sure it has to do with the inner joins narrowing my search down.  
    Has anyone tweaked this to work to show ALL the jobs on a server?  

    Thanks, Henry!!

    It's for scheduled jobs. If you look at the joins, the jobs would need a schedule as well as history to show up. If you wanted every job, not just scheduled jobs, try changing the joins to this to get every job: 

    FROM msdb..sysjobhistory d
     RIGHT JOIN msdb..sysjobs a
       ON a.job_id = d.job_id
     LEFT JOIN msdb..sysJobschedules c
       ON a.job_id = c.job_id
     LEFT JOIN msdb..SysSchedules b
       ON b.Schedule_id=c.Schedule_id
    ORDER BY [Job Name]

    Sue

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

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