704
Problem
SQL Server Agent Jobs have lots of information associated with the job, steps, history, and schedules. You can get a lot of this data using SSMS, but sometimes querying the SQL Server jobs might be easier. In this article we look at several SQL queries you can use to get SQL Server Agent Job information to better manage SQL jobs.
Solution
In this tip, we will see how to query some basic job monitoring and job setup information. The intention of this tip is to act as a starting point for people that need to monitor SQL Server Agent Jobs.
Before we get into querying the required information, let’s get a basic understanding of msdb database. The msdb is a SQL Server System Database and is used to store the Configuration, Processing, and Metadata information related to the SQL Server features, including:
- SQL Server Agent Jobs, Job Steps, Job schedules, Alerts, Operators, etc.
- Service Broker, Log Shipping, database backups and restore information, Maintenance Plan, Database Mail, Policy Based Information, etc.
- SQL Server Integration Services (SSIS) packages stored in the database.
In this tip, we will retrieve the following information related to SQL Server Agent Jobs by querying some of the system tables in the msdb database:
- Job Setup/Configuration Information
- Job Execution Information
- Job Step(s) Setup/Configuration Information
- Job Step(s) Execution Information
- Schedule Information
SQL Server Agent Job Setup and Configuration Query
SQL Server allows creation of jobs for performing various tasks in an automated fashion like Replication, running SSIS Packages, Stored Procedures, Batch Commands, etc. These jobs can be created either using SSMS GUI or using T-SQL queries. Irrespective of the approach used, this information is stored in a set of SQL Server System Tables present in the msdb database.
The following query gives us the Job Level Setup and Configuration information which is also found in the SQL Server Agent Job Properties window in SSMS.
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, [sDBP].[name] AS [JobOwner]
, [sCAT].[name] AS [JobCategory]
, [sJOB].[description] AS [JobDescription]
, CASE [sJOB].[enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
, [sJOB].[date_created] AS [JobCreatedOn]
, [sJOB].[date_modified] AS [JobLastModifiedOn]
, [sSVR].[name] AS [OriginatingServerName]
, [sJSTP].[step_id] AS [JobStartStepNo]
, [sJSTP].[step_name] AS [JobStartStepName]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
ELSE 'Yes'
END AS [IsScheduled]
, [sSCH].[schedule_uid] AS [JobScheduleID]
, [sSCH].[name] AS [JobScheduleName]
, CASE [sJOB].[delete_level]
WHEN 0 THEN 'Never'
WHEN 1 THEN 'On Success'
WHEN 2 THEN 'On Failure'
WHEN 3 THEN 'On Completion'
END AS [JobDeletionCriterion]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
ON [sJOB].[category_id] = [sCAT].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
ON [sJOB].[job_id] = [sJSTP].[job_id] AND [sJOB].[start_step_id] = [sJSTP].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
ON [sJOB].[owner_sid] = [sDBP].[sid]
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]
ORDER BY [JobName]Description of Query Columns
The following is a brief description of each of the fields returned from the above query:
- [JobID]: A unique identifier for the SQL Server Agent job (GUID).
- [JobName]: Name of the SQL Server Agent job.
- [JobOwner]: Owner of the job.
- [JobCategory]: Category to which the job belongs like Replication Snapshot, Database Maintenance, Log Shipping, etc.
- [JobDescription]: Description of the job.
- [IsEnabled]: Indicator representing whether the job is enabled or disabled.
- [JobCreatedOn]: Date and time when the job was created.
- [JobLastModifiedOn]: Date and time when the job was last modified.
- [OriginatingServerName]: Server from which the job executed.
- [JobStartStepNo]: Step number from which the job is set to start. SQL Server allows us to have multiple steps within a job and the job can be set to start from whichever step the user wants it to start from.
- [JobStartStepName]: Name of the step from which the job is set to start.
- [IsScheduled]: Indicator representing whether the job is scheduled or not. The jobs can be either scheduled to run on specified day(s) at a specified time or can be invoked through code like T-SQL, etc.
- [JobScheduleID]: Unique identifier of the schedule associated with the job (GUID).
- [JobScheduleName]: Name of the schedule associated with the job. SQL Server allows us to associate multiple schedules with one job, in which case, the above query would return one row for each schedule associated with each job.
- [JobDeletionCriterion]: The criterion for deleting the job. SQL Server Agent has a feature which allows us to delete/drop the job based on a certain criterion so that there is no need to manually delete/cleanup the jobs.
SQL Server Agent Job Execution Query
SQL Server Agent stores the history of job execution in system tables in msdb database.
The following query gives us the details of last/latest execution of the SQL Server Agent Job and also the next time when the job is going to run (if it is scheduled). This information can also be found in the Job History/Job Activity Monitor windows in SSMS.
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, CASE
WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
ELSE CAST(
CAST([sJOBH].[run_date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [LastRunDateTime]
, CASE [sJOBH].[run_status]
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'Running' -- In Progress
END AS [LastRunStatus]
, STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)]
, [sJOBH].[message] AS [LastRunStatusMessage]
, CASE [sJOBSCH].[NextRunDate]
WHEN 0 THEN NULL
ELSE CAST(
CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [NextRunDateTime]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN (
SELECT
[job_id]
, MIN([next_run_date]) AS [NextRunDate]
, MIN([next_run_time]) AS [NextRunTime]
FROM [msdb].[dbo].[sysjobschedules]
GROUP BY [job_id]
) AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN (
SELECT
[job_id]
, [run_date]
, [run_time]
, [run_status]
, [run_duration]
, [message]
, ROW_NUMBER() OVER (
PARTITION BY [job_id]
ORDER BY [run_date] DESC, [run_time] DESC
) AS RowNumber
FROM [msdb].[dbo].[sysjobhistory]
WHERE [step_id] = 0
) AS [sJOBH]
ON [sJOB].[job_id] = [sJOBH].[job_id]
AND [sJOBH].[RowNumber] = 1
ORDER BY [JobName]Description of Query Columns
The following is a brief description of each of the fields returned from the above query:
- [JobID]: A unique identifier for the SQL Server Agent job (GUID) for which the execution history is being reported.
- [JobName]: Name of the SQL Server Agent job.
- [LastRunDateTime]: Date and time when the job was run last time (corresponds to the most recent run).
- [LastRunStatus]: Status or outcome of the last job run.
- [LastRunDuration (HH:MM:SS)]: Duration of the last run represented in Hours:Minutes:Seconds format.
- [LastRunStatusMessage]: More descriptive message about the job status/outcome.
- [NextRunDateTime]: Date and time when the job will run next time. This information is available only for the jobs which are scheduled (a schedule is associated with a job).
SQL Server Agent Job Steps Setup and Configuration Query
In SQL Server Agent, a job is the parent-level entity, which contains one or more steps. A step is the child/low-level entity, which contains the actual commands/instructions for performing a specific task. Whereas a job defines the sequence of execution of steps, schedule for running steps, etc.
The following query gives us the Job Step level Setup and Configuration information, which can also be found in the Job Step Properties window in SSMS.
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, [sJSTP].[step_uid] AS [StepID]
, [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]
, [sPROX].[name] AS [RunAs]
, [sJSTP].[database_name] AS [Database]
, [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 [RetryInterval (Minutes)]
, 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]
FROM
[msdb].[dbo].[sysjobsteps] AS [sJSTP]
INNER 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]
ORDER BY [JobName], [StepNo]Description of Query Columns
The following is a brief description of each of the fields returned from the above query:
- [JobID]: A unique identifier for the SQL Server Agent job (GUID) to which the step(s) belongs.
- [JobName]: Name of the SQL Server Agent job.
- [StepID]: A unique identifier for the SQL Server Agent Job Step (GUID).
- [StepNo]: Sequence number of the step representing the position of the step in the job.
- [StepName]: Name of the SQL Server Agent Job Step.
- [StepType]: Subsystem/Type of the Job Step like SQL Server Integration Services Package, Transact-SQL Script (T-SQL), ActiveX Script etc.
- [RunAs]: Account under which the job step should be run/executed. This will contain a value in the above query output only when run through a proxy.
- [Database]: Name of the database in which the command is executed. This applies only when the Step Type is Transact-SQL Script (T-SQL).
- [ExecutableCommand]: The actual command which will be executed by the subsystem.
- [OnSuccessAction]: Action to be taken by SQL Server Agent when the job step succeeds.
- [RetryAttempts]: Number of retry attempts made by SQL Server Agent in case the job step fails.
- [RetryInterval (Minutes)]: Time interval in minutes between each retry attempt in case the job step fails and SQL Server Agent tries to re-run it.
- [OnFailureAction]: Action to be taken by SQL Server Agent when the job step fails.
SQL Server Agent Job Steps Execution Query
SQL Server Agent stores the history of the execution of each of the job steps in system tables in msdb database.
The following query gives us the details of last/latest execution of the job step. This information can also be found in the Job History/Log File Viewer windows in SSMS.
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, [sJSTP].[step_uid] AS [StepID]
, [sJSTP].[step_id] AS [StepNo]
, [sJSTP].[step_name] AS [StepName]
, 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]
, STUFF(
STUFF(RIGHT('000000' + CAST([sJSTP].[last_run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LastRunDuration (HH:MM:SS)]
, [sJSTP].[last_run_retries] AS [LastRunRetryAttempts]
, 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]
FROM
[msdb].[dbo].[sysjobsteps] AS [sJSTP]
INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
ON [sJSTP].[job_id] = [sJOB].[job_id]
ORDER BY [JobName], [StepNo]Description of Query Columns
The following is a brief description of each of the fields returned from the above query:
- [JobID]: A unique identifier for the SQL Server Agent job (GUID) to which the step(s) belongs.
- [JobName]: Name of the SQL Server Agent job.
- [StepID]: A unique identifier for the SQL Server Agent Job Step (GUID).
- [StepNo]: Sequence number of the step representing the position of the step in the job.
- [StepName]: Name of the SQL Server Agent Job Step.
- [LastRunStatus]: Status or Outcome of the step execution when the job/step executed last time.
- [LastRunDuration (HH:MM:SS)]: Duration of the last run represented in Hours:Minutes:Seconds format.
- [LastRunRetryAttempts]: Number of retry attempts made by SQL Server Agent during the last run of the job step.
- [LastRunDateTime]: Date and time when the job step was last run.
SQL Server Agent Job Schedule Query
SQL Server allows creating schedules for performing various tasks at a specified date and time. This can be a one time schedule or a recurring schedule with or without an end date. Each schedule can be associated with one or more SQL Server Agent Jobs.
The following query gives us the list of schedules created/available in SQL Server and the details (Occurrence, Recurrence, Frequency, etc.) of each of the schedules.
SELECT
[schedule_uid] AS [ScheduleID]
, [name] AS [ScheduleName]
, CASE [enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
, CASE
WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
WHEN [freq_type] = 1 THEN 'One Time'
END [ScheduleType]
, CASE [freq_type]
WHEN 1 THEN 'One Time'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly – Relative to Frequency Interval'
WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
WHEN 128 THEN 'Start whenever the CPUs become idle'
END [Occurrence]
, CASE [freq_type]
WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ' week(s) on '
+ CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
+ CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
+ CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
+ CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
+ CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
+ CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
+ CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3))
+ ' of every '
+ CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
WHEN 32 THEN 'Occurs on '
+ CASE [freq_relative_interval]
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 4 THEN 'Third'
WHEN 8 THEN 'Fourth'
WHEN 16 THEN 'Last'
END
+ ' '
+ CASE [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 'Weekday'
WHEN 10 THEN 'Weekend day'
END
+ ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ' month(s)'
END AS [Recurrence]
, CASE [freq_subday_type]
WHEN 1 THEN 'Occurs once at '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 2 THEN 'Occurs every '
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
+ ' & '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 4 THEN 'Occurs every '
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
+ ' & '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
WHEN 8 THEN 'Occurs every '
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
+ ' & '
+ STUFF(
STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
END [Frequency]
, STUFF(
STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
, 8, 0, '-') AS [ScheduleUsageStartDate]
, STUFF(
STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
, 8, 0, '-') AS [ScheduleUsageEndDate]
, [date_created] AS [ScheduleCreatedOn]
, [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysschedules]
ORDER BY [ScheduleName]Description of Query Columns
The following is a brief description of each of the fields returned from the above query:
- [ScheduleID]: Unique identifier of the schedule (GUID).
- [ScheduleName]: Name of the schedule. SQL Server allows one schedule to be associated with more than one job.
- [IsEnabled]: Indicator representing whether a schedule is enabled or disabled.
- [ScheduleType]: The type of the schedule.
- [Occurrence]: Occurrence of the schedule like Daily, Weekly, Monthly, etc.
- [Recurrence]: Recurrence of the schedule like specific day(s), Specific Days of the Week, Number of weeks, etc.
- [Frequency]: How frequently the job should run on the day(s) when it is scheduled to run such as: Occurs only once on the scheduled day(s), Occurs every 2 hours on the scheduled day(s) etc. between specified start and end times.
- [ScheduleUsageStartDate]: Effective start date from when the schedule will be used.
- [ScheduleUsageEndDate]: Effective end date after which the schedule will not be used.
- [ScheduleCreatedOn]: Date and time when the schedule was created.
- [ScheduleLastModifiedOn]: Date and time when the schedule was last modified.
Next Steps
- Review the scripts in this tip and start extending them to meet your monitoring and documentation requirements.
- Write scripts to get the following information:
- Get the list of long running jobs
- Get the list of jobs scheduled to run in next 24/48 hours
- Get the list of jobs failed in last 24/48 hours
- Check out other SQL Server Agent related tips
- Check out these additional resources:
