The following system stored procedure can be used to retrieve information about a SQL Server Agent job:
EXECUTE msdb.dbo.sp_help_job
@job_name = 'jobNo1',
@job_id = 1;
Either the job name or the job id can be provided. If neither job name or id is provided, then information about all jobs currently defined in the msdb
database will be returned.
There are also other options for filtering out specific types of jobs. A few useful ones are the parameters @enabled
and @execution_status
.
The procedure returns four result sets with information about different aspects of a job: job (JOB
), schedule (SCHEDULES
), job step (STEPS
) and target (TARGETS
). Using the @job_aspect
parameter, it is possible to control which of the four result sets should be returned.
SQL Server has some system tables storing information that is used by SQL Server Agent. These can be used to get an overview of the status of jobs.
The following script is an example using only a few of the system tables to return jobs currently running:
SELECT
job.[name],
job.job_id,
job.originating_server,
activity.run_requested_date,
DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) as elapsed
FROM msdb.dbo.sysjobs_view AS job
INNER JOIN msdb.dbo.sysjobactivity AS activity
ON job.job_id = activity.job_id
INNER JOIN msdb.dbo.syssessions AS sess
ON sess.session_id = activity.session_id
INNER JOIN (SELECT MAX(agent_start_date) AS max_agent_start_date FROM msdb.dbo.syssessions) AS sess_max
ON sess.agent_start_date = sess_max.max_agent_start_date
WHERE run_requested_date IS NOT NULL AND stop_execution_date IS NULL;
For more information, see Microsoft's documentation: