SQL Server Agent
Create Job
In SSMS Object Explorer connected to your target SQL Server...
SQL Server Agent - Jobs - Right Click - New Job...
Duplicate Job
In SSMS Object Explorer connected to your target SQL Server...
SQL Server Agent - Jobs - Right Click - Script Job as - CREATE To - File...
Make any necessary changes to the generated script (e.g. change database names etc), then run it in the target instance
List Jobs
SELECT name,
enabled,
description
FROM msdb.dbo.sysjobs
List Jobs with a Schedule
SELECT name,
enabled,
description
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
ORDER BY enabled DESC
To include schedule name...
SELECT sysjobs.job_id,
sysjobs.name job_name,
sysjobs.enabled job_enabled,
sysschedules.name schedule_name,
sysschedules.schedule_id,
sysschedules.schedule_uid,
sysschedules.enabled schedule_enabled
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
INNER JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
ORDER BY sysjobs.enabled desc
To include Frequency and start time...
SELECT sysjobs.name job_name,
sysjobs.enabled job_enabled,
sysschedules.name schedule_name,
sysschedules.enabled schedule_enabled,
"Freq" = CASE sysschedules.freq_type
WHEN '1' THEN 'Once'
WHEN '4' THEN 'Daily'
WHEN '8' THEN 'Weekly'
WHEN '16' THEN 'Monthly'
WHEN '32' THEN 'Monthly (relative to freq_interval)'
WHEN '64' THEN 'At Agent Start'
WHEN '128' THEN 'When Idle'
ELSE 'Unexpected'
END,
sysschedules.freq_interval,
sysschedules.freq_subday_type,
sysschedules.freq_subday_interval,
sysschedules.freq_recurrence_factor,
sysschedules.active_start_time
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobschedules
ON sysjobs.job_id = sysjobschedules.job_id
INNER JOIN msdb.dbo.sysschedules
ON sysjobschedules.schedule_id = sysschedules.schedule_id
ORDER BY sysjobs.enabled DESC
List Jobs without a Schedule
SELECT name,
enabled,
description
FROM msdb.dbo.sysjobs
WHERE job_id IN ( SELECT job_id
FROM msdb.dbo.sysjobs
EXCEPT
SELECT job_id
FROM msdb.dbo.sysjobschedules )
List Schedules
SELECT name,
enabled,
freq_type,
freq_interval,
freq_subday_type,
freq_subday_interval,
freq_recurrence_factor
FROM msdb.dbo.sysschedules
SQL Server Agent Job Information
/*
PURPOSE: Querying SQL Server Agent Job Information
AUTHOR: Dattatrey Sindol
SOURCE: https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/
CREATED: 09-DEC-2011
HISTORY:
19-DEC-2023 MPG Formatting
02-MAR-2021 L.Gurdian added JobOwner
02-JAN-2021 Corey Bui added actual schedule
02-JAN-2021 L.Gurdian Added 1.) The purpose
2.) The schedule for the job
3.) If it’s scheduled or not
4.) Originating server
*/
SELECT [sJOB].[name] AS JobName,
[sJOB].[description] AS JobDescription,
[sJSTP].[step_id] AS StepNo,
[sJSTP].[step_name ] AS StepName,
CASE [sJSTP].[subsystem]
WHEN 'ActiveScripting' THEN 'ActiveX Script'
WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
WHEN 'PowerShell' THEN 'PowerShell'
WHEN 'Distribution' THEN 'Replication Distributor'
WHEN 'Merge' THEN 'Replication Merge'
WHEN 'QueueReader' THEN 'Replication Queue Reader'
WHEN 'Snapshot' THEN 'Replication Snapshot'
WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
ELSE [sJSTP].[subsystem]
END AS [StepType],
[sDBP].[name] AS [JobOwner],
[sPROX].[name] AS [RunAs],
[sSVR].[name] AS [OriginatingServerName],
[sJSTP].[database_name] AS [DatabaseName],
CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
ELSE 'Yes'
END AS [IsScheduled],
CASE
WHEN [sSCH].[enabled] = 1 THEN 'Yes'
ELSE 'No'
END AS [ScheduleEnabled],
[sSCH].[name] AS [JobScheduleName],
CASE
WHEN [sSCH].[freq_type] = 1
THEN 'Once, '
+ ' starting at '
+ CAST([sSCH].[active_start_date] AS VARCHAR(100))
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6)
+ CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN [sSCH].[freq_type] = 4
THEN 'Daily, '
+ 'every '
+ CAST([sSCH].[freq_interval] AS VARCHAR(3))
+ ' day(s), '
+ CASE
WHEN [sSCH].[freq_subday_type] = 2
THEN ' every '
+ CAST([sSCH].[freq_subday_interval] AS VARCHAR(7))
+ ' seconds'
+ ' starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6)
+ CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN [sSCH].[freq_subday_type] = 4
THEN ' every '
+ CAST([sSCH].[freq_subday_interval] AS VARCHAR(7))
+ ' minutes'
+ ' starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6)
+ CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN [sSCH].[freq_subday_type] = 8
THEN ' every '
+ CAST([sSCH].[freq_subday_interval] AS VARCHAR(7))
+ ' hours'
+ ' starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6)
+ CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
ELSE ' starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6)
+ CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
END
WHEN [sSCH].[freq_type] = 8
THEN 'Weekly, '
+ REPLACE (CASE WHEN [sSCH].freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN [sSCH].freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN [sSCH].freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN [sSCH].freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN [sSCH].freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN [sSCH].freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN [sSCH].freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END,
', ',
'')
+ ', '
+ CASE
WHEN [sSCH].[freq_subday_type] = 2
THEN ' every '
+ CAST([sSCH].[freq_subday_interval] AS VARCHAR(7))
+ ' seconds'
+ ' starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6)
+ CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN [sSCH].[freq_subday_type] = 4
THEN ' every '
+ CAST([sSCH].[freq_subday_interval] AS VARCHAR(7))
+ ' minutes'
+ ' starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6)
+ CAST([sSCH].[active_start_time] as VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
WHEN [sSCH].[freq_subday_type] = 8
THEN ' every '
+ CAST([sSCH].[freq_subday_interval] AS VARCHAR(7))
+ ' hours'
+ ' starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6)
+ CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
ELSE ' starting at '
+ STUFF(STUFF(RIGHT(REPLICATE('0', 6)
+ CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
END
WHEN [sSCH].[freq_type] = 16
THEN 'Monthly, '
+ 'on day '
+ CAST([sSCH].[freq_interval] AS VARCHAR(3))
WHEN [sSCH].[freq_type] = 32
THEN 'Monthly, '
+ 'on week '
+ CAST([sSCH].[freq_relative_interval] AS VARCHAR(100))
+ ', '
+ CASE [sSCH].[freq_interval]
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'Day'
WHEN 9 THEN 'Weekend day'
END
+ ' every '
+ CAST([sSCH].[freq_recurrence_factor] AS VARCHAR(100))
+ ' months'
WHEN [sSCH].[freq_type] = 64
THEN 'Auto starts when SQL Agent starts up'
ELSE ''
END AS [ActualSchedule],
[sJSTP].[command] AS [ExecutableCommand],
CASE [sJSTP].[on_success_action]
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4 THEN 'Go to Step: '
+ QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
+ ' '
+ [sOSSTP].[step_name]
END AS [OnSuccessAction],
[sJSTP].[retry_attempts] AS [RetryAttempts],
[sJSTP].[retry_interval] AS [RetryIntervalInMinutes],
CASE [sJSTP].[on_fail_action]
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4 THEN 'Go to Step: '
+ QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
+ ' '
+ [sOFSTP].[step_name]
END AS [OnFailureAction],
CASE [sJSTP].[last_run_date]
WHEN 0 THEN NULL
ELSE CAST(CAST([sJSTP].[last_run_date] AS CHAR(8))
+ ' '
+ STUFF(STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
AS DATETIME)
END AS [LastRunDateTime],
STUFF(STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)],
CASE [sJSTP].[last_run_outcome]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 5 THEN 'Unknown'
END AS [LastRunStatus]
FROM [msdb].[dbo].[sysjobsteps] AS [sJSTP]
JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP] ON [sJSTP].[job_id] = [sOSSTP].[job_id]
AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP] ON [sJSTP].[job_id] = [sOFSTP].[job_id]
AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX] ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
LEFT JOIN [msdb].[sys].[servers] AS [sSVR] ON [sJOB].[originating_server_id] = [sSVR].[server_id]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]
WHERE [sJOB].[enabled] = 1
ORDER BY [sJOB].[name],
[sJSTP].[step_id]
Identifying Unknown Jobs
Sometimes jobs appear in the Job Activity Monitor that you may not recognise. This section aims to help identify some of these jobs...
Jobs starting CDW relate to the Copy Database Wizard
Troubleshooting
If a SQL Agent job has not run...
Check the database/server was up at the time the job was scheduled to run.
Check that the following Services are running: Task Scheduler, SQL Server Agent, Windows Event Log.
Check that the job is Enabled.
Check that the job schedule is Enabled.
Check the logs.