dbo.sysjobactivity (Transact-SQL)


Updated: August 5, 2016

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 nameData typeDescription
session_idintID of the session stored in the syssessions table in the msdb database.
job_iduniqueidentifierID of the job.
run_requested_datedatetimeDate and time that the job was requested to run.
run_requested_sourcesysname(nvarchar(128))Who requested the job to run.





queued_datedatetimeDate and time this job was queued. If the job is run directly, this column is NULL.
start_execution_datedatetimeDate and time job has been scheduled to run.
last_executed_step_idintID of the last job step that ran.

datetimeDate and time that the last job step began to run.
stop_execution_datedatetimeDate and time that the job finished running.
job_history_idintUsed to identify a row in the sysjobhistory table.
next_scheduled_run_datedatetimeNext date and time that the job is scheduled to run.

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, 
        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); 

dbo.sysjobhistory (Transact-SQL)

Community Additions