Search code examples
sql-serversql-server-2008t-sqlsql-agent

how to know status of currently running jobs

I need to know if a given Job is currently running on Ms SQL 2008 server. So as to not to invoke same job again that may lead to concurrency issues.

Solution

  • It looks like you can use msdb.dbo.sysjobactivity, checking for a record with a non-null start_execution_date and a null stop_execution_date, meaning the job was started, but has not yet completed.

    This would give you currently running jobs:

    SELECT sj.name
   , sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
   AND sja.stop_execution_date IS NULL