Get a list of SQL Server Agent Jobs and their schedules

On 19 May, 2015

MS SQL DBA

By : Charith Silva

No Comments

Sometime you might want to get a list of SQL Server Agent Jobs and their schedules on your current SQL server instance. You could manually open each and every Agent job through SSMS and note down the details you required, but it might take long time depend on number of jobs on the server. But below script will help you to get detailed list of SQL Server Agent Jobs at once.

SELECT DISTINCT a.name AS [SQL Agent Job Name], 
CASE 
WHEN a.enabled = 0 THEN 'Disabled'
WHEN a.enabled = 1 THEN 'Enabled'
end AS [Job Status],
a.date_Created AS [Job Created Date], 
b.name AS [Schedule Name],
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 to interval' 
WHEN b.freq_type = 64 THEN 'Runs when the SQL Server Agent service starts'
WHEN b.freq_type = 128 THEN 'Runs when the computer is idle'
END AS [Job Running Frequency], 
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 as [Interval], 
b.date_created as [Schedule created date], 
CAST(CAST(b.active_start_date AS VARCHAR(10)) as date) AS [Execution begin],
CAST(CAST(b.active_end_date AS VARCHAR(10))as date) AS [Execution stop]

FROM msdb.dbo.sysjobs a 
LEFT JOIN msdb.dbo.sysJobschedules c ON a.job_id = c.job_id 
LEFT JOIN msdb.dbo.SysSchedules b on b.Schedule_id=c.Schedule_id

SELECT DISTINCT a.name AS [SQL Agent Job Name],

CASE

WHEN a.enabled = 0 THEN 'Disabled'

WHEN a.enabled = 1 THEN 'Enabled'

end AS [Job Status],

a.date_Created AS [Job Created Date],

b.name AS [Schedule Name],

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 to interval'

WHEN b.freq_type = 64 THEN 'Runs when the SQL Server Agent service starts'

WHEN b.freq_type = 128 THEN 'Runs when the computer is idle'

END AS [Job Running Frequency],

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 as [Interval],

b.date_created as [Schedule created date],

CAST(CAST(b.active_start_date AS VARCHAR(10)) as date) AS [Execution begin],

CAST(CAST(b.active_end_date AS VARCHAR(10))as date) AS [Execution stop]

FROM msdb.dbo.sysjobs a

LEFT JOIN msdb.dbo.sysJobschedules c ON a.job_id = c.job_id

LEFT JOIN msdb.dbo.SysSchedules b on b.Schedule_id=c.Schedule_id

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva