Applies to: SQL Server
Records current SQL Server Agent job activity and status. This table is stored in the msdb database.
|ID of the session stored in the syssessions table in the msdb database.
|ID of the job.
|Date and time that the job was requested to run.
|Who requested the job to run.
1 = SOURCE_SCHEDULER
2 = SOURCE_ALERTER
3 = SOURCE_BOOT
4 = SOURCE_USER
6 = SOURCE_ON_IDLE_SCHEDULE
|Date and time this job was queued. If the job is run directly, this column is NULL.
|Date and time job has been scheduled to run.
|ID of the last job step that ran.
|Date and time that the last job step began to run.
|Date and time that the job finished running.
|Used to identify a row in the sysjobhistory table.
|Next 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.
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);