is a specific sql agent job running right now

Question

hi we run 2019 enterprise. i would like to know before kicking off another process whether a specific sql agent job is running.

will this code tell me what i want? is it overly simplified? isnt it true that sometimes jobs go into a suspended state and this may show something running that isnt really running?

use msdb  
SELECT b.name,a.*  
  FROM [sysjobactivity] a  
  join sysjobs b  
  on a.job_id=b.job_id  
  
  where (start_execution_date is not null) and   
        stop_execution_date is null and  
 name='the job that cant be running when my process starts'  

Answer 1

That query is for the "job log", in theory it represents the current state of the SQL Agent Job. However, there are edge cases where the log will not be updated and still show the job as "running" and the job is not actually running. For example, if the server is powered off or SQL Agent crashes while a job is running. The log will still show it is running. There is no way in SQL Server to query SQL Agent to get what is actually running. So that is the best you can do.

Answer 2

Hi @db042190 ,

The following query lists all jobs run in the last 24 hours, along with the time run, the outcome, and whether the job is enabled :

USE MSDB  
SELECT name AS [Job Name]  
         ,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */   
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */   
          + (run_time - (run_time/100) * 100)  /* secs */  
           ,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]  
         ,CASE WHEN enabled=1 THEN 'Enabled'   
               ELSE 'Disabled'   
          END [Job Status]  
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'  
                     WHEN SJH.run_status=1 THEN 'Succeeded'  
                     WHEN SJH.run_status=2 THEN 'Retry'  
                     WHEN SJH.run_status=3 THEN 'Cancelled'  
               ELSE 'Unknown'   
          END [Job Outcome]  
FROM   sysjobhistory SJH   
JOIN   sysjobs SJ   
ON     SJH.job_id=sj.job_id   
WHERE  step_id=0   
AND    DATEADD(S,   
  (run_time/10000)*60*60 /* hours */   
  +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */   
  + (run_time - (run_time/100) * 100)  /* secs */,   
  CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-1,GetDate())   
ORDER BY name,run_date,run_time   

This uses the sysjobhistory and sysjobs system tables in the msdb database. One curious aspect of these tables is that they store dates and times as a numerical respresentation of a time and date . To make this more readable I've converted this into a more conventional format.

To obtain status information about each job you can run the following stored procedure, (this is actually what Job Activity Monitor uses) :

exec msdb.dbo.sp_help_job  

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

thx Niko. I need to know what's running right now and if there is a chance an abort or something would make it appear its still running per my query rules, i'd like to know how to recognize that. I am already familiar with what you showed.

This is what i wrote in the original post...whether a specific sql agent job is running

Answer 4

thx tom, and i hit that edge case just as we went to qa. there is a sysjobactivity record left out there from 2021. we'll probably put an extra condition in where the start date has to be say within the last x number of hrs or so.

Answer 5

looking to see if joining to the table that indicates success/failure might help me. the one niko showed. unfortunately history doesnt go back that far so the record i hit leaves me in limbo. asking dba to delete it. apparently we have no such loiterers now sitting in production.