Many companies rely on the job scheduling and alerting services provided with the native SQL Server Agent service. From backups to SSRS subscription to SSIS packages, SQL Server Agent has the capability to run many types of business critical tasks on a regular schedule or can be programmatically triggered to start from other processes as part of an overall workflow whose processing times vary daily. It is easy for the quantity and complexity of the SQL Agent jobs to get out of control. If you have hundreds of jobs, for example, each with its own schedule and code, it can be time consuming to find and address any issues that may arise. Fortunately, all of the metadata you need for your SQL Agent jobs is stored in MSDB and can be queried.
One Query Provides Useful Information
Below is a simple script that interrogates the MSDB SQL Agent Job and JobHistory tables and returns useful information to help determine what each job is executing at each step, how frequently the jobs runs each day and what is the average duration. Notice the use of the system function agent_datetime that takes the integer values of run_date and run_time and converts them handily into the more familiar datetime format.
SELECT job.name,
CAST(dbo.agent_datetime(run_date, run_time) AS DATE) AS fRun_Date,
dbo.agent_datetime(run_date, run_time) AS fRun_date_time,
STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(run_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
STUFF(
STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(run_duration AS VARCHAR(8)), 8), 3, 0, ':'), 6, 0, ':'),
9,
0,
':'
) 'duration',
instance_id,
step.command,
history.job_id,
history.step_id,
sql_message_id,
sql_severity,
message,
run_status,
run_date,
run_time,
run_duration,
AVG(history.run_duration) OVER (PARTITION BY history.job_id) AS average_rt,
MAX(history.run_duration) OVER (PARTITION BY history.job_id) AS max_rt,
MIN(history.run_duration) OVER (PARTITION BY history.job_id) AS min_rt,
COUNT(history.job_id) OVER (PARTITION BY history.job_id, run_date) AS execution_day,
DATEPART(HOUR, dbo.agent_datetime(run_date, run_time)) AS Hour_day,
COUNT(history.job_id) OVER (PARTITION BY history.job_id,
DATEPART(HOUR, dbo.agent_datetime(run_date, run_time))) AS execution_hour, history.server
FROM sysjobhistory history
INNER JOIN sysjobs job
ON job.job_id = history.job_id
LEFT OUTER JOIN [dbo].[sysjobsteps] step
ON step.job_id = job.job_id
AND step.step_id = job.start_step_id
WHERE history.step_id = 0
AND CAST(dbo.agent_datetime(run_date, run_time) AS DATE) >= GETDATE() - 200
ORDER BY job.job_id,
fRun_date_time DESC;
The results show not only the job name but also the command that is being issued when the job runs as well as a lot of information about the execution statistics. In this case the script will only pull information for the overall job which is step “0”. This is done for a concise aggregation. It is possible to get information about individual job step execution times as well but this would require a slight adjustment to query.
For more information on SQL Agent jobs,
contact us. We’re here to help!
