MSSQL Agent Jobs
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