dbo.sysjobactivity (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Records current SQL Server Agent job activity and status. This table is stored in the msdb database.

Column name Data type Description
session_id int ID of the session stored in the syssessions table in the msdb database.
job_id uniqueidentifier ID of the job.
run_requested_date datetime Date and time that the job was requested to run.
run_requested_source sysname(nvarchar(128)) Who requested the job to run.

1 = SOURCE_SCHEDULER

2 = SOURCE_ALERTER

3 = SOURCE_BOOT

4 = SOURCE_USER

6 = SOURCE_ON_IDLE_SCHEDULE
queued_date datetime Date and time this job was queued. If the job is run directly, this column is NULL.
start_execution_date datetime Date and time job has been scheduled to run.
last_executed_step_id int ID of the last job step that ran.
last_executed_step_

date
datetime Date and time that the last job step began to run.
stop_execution_date datetime Date and time that the job finished running.
job_history_id int Used to identify a row in the sysjobhistory table.
next_scheduled_run_date datetime Next date and time that the job is scheduled to run.

Example

This example will return the run-time status for all SQL Server Agent jobs. Execute the following Transact-SQL in SQL Server Management Studio.

SELECT sj.Name, 
    CASE
        WHEN sja.start_execution_date IS NULL THEN 'Not running'
        WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
        WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
    END AS 'RunStatus'
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
WHERE session_id = (
    SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity); 

See Also

dbo.sysjobhistory (Transact-SQL)