how to know status of currently running jobs

Question

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 1

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

Solution 2

I found a better answer by Kenneth Fisher. The following query returns only currently running jobs:

SELECT
    ja.job_id,
    j.name AS job_name,
    ja.start_execution_date,      
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
    ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE
  ja.session_id = (
    SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC
  )
AND start_execution_date is not null
AND stop_execution_date is null;

You can get more information about a job by adding more columns from msdb.dbo.sysjobactivity table in select clause.

Solution 3

EXEC msdb.dbo.sp_help_job @Job_name = 'Your Job Name'

check field execution_status

0 - Returns only those jobs that are not idle or suspended.
1 - Executing.
2 - Waiting for thread.
3 - Between retries.
4 - Idle.
5 - Suspended.
7 - Performing completion actions.

If you need the result of execution, check the field last_run_outcome

0 = Failed
1 = Succeeded
3 = Canceled
5 = Unknown

https://msdn.microsoft.com/en-us/library/ms186722.aspx

Solution 4

Given a job (I assume you know its name) you can use:

EXEC msdb.dbo.sp_help_job @Job_name = 'Your Job Name'

as suggested in MSDN Job Help Procedure. It returns a lot of informations about the job (owner, server, status and so on).

Solution 5

This query will give you the exact output for current running jobs. This will also shows the duration of running job in minutes.

   WITH
    CTE_Sysession (AgentStartDate)
    AS 
    (
        SELECT MAX(AGENT_START_DATE) AS AgentStartDate FROM MSDB.DBO.SYSSESSIONS
    )   
SELECT sjob.name AS JobName
        ,CASE 
            WHEN SJOB.enabled = 1 THEN 'Enabled'
            WHEN sjob.enabled = 0 THEN 'Disabled'
            END AS JobEnabled
        ,sjob.description AS JobDescription
        ,CASE 
            WHEN ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NULL  THEN 'Running'
            WHEN ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NOT NULL AND HIST.run_status = 1 THEN 'Stopped'
            WHEN HIST.run_status = 0 THEN 'Failed'
            WHEN HIST.run_status = 3 THEN 'Canceled'
        END AS JobActivity
        ,DATEDIFF(MINUTE,act.start_execution_date, GETDATE()) DurationMin
        ,hist.run_date AS JobRunDate
        ,run_DURATION/10000 AS Hours
        ,(run_DURATION%10000)/100 AS Minutes 
        ,(run_DURATION%10000)%100 AS Seconds
        ,hist.run_time AS JobRunTime 
        ,hist.run_duration AS JobRunDuration
        ,'tulsql11\dba' AS JobServer
        ,act.start_execution_date AS JobStartDate
        ,act.last_executed_step_id AS JobLastExecutedStep
        ,act.last_executed_step_date AS JobExecutedStepDate
        ,act.stop_execution_date AS JobStopDate
        ,act.next_scheduled_run_date AS JobNextRunDate
        ,sjob.date_created AS JobCreated
        ,sjob.date_modified AS JobModified      
            FROM MSDB.DBO.syssessions AS SYS1
        INNER JOIN CTE_Sysession AS SYS2 ON SYS2.AgentStartDate = SYS1.agent_start_date
        JOIN  msdb.dbo.sysjobactivity act ON act.session_id = SYS1.session_id
        JOIN msdb.dbo.sysjobs sjob ON sjob.job_id = act.job_id
        LEFT JOIN  msdb.dbo.sysjobhistory hist ON hist.job_id = act.job_id AND hist.instance_id = act.job_history_id
        WHERE ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NULL
        ORDER BY ACT.start_execution_date DESC

View more solutions

Comments

[Paul]

This reports some non-running jobs on my servers.

[Paul]

I don't know enough about sysjobactivity. I just know I have a job that matches your where criteria for an activity several activties in the past. i.e. not the current or latest. The job is currently idle. I don't know if this represents a cancel, or an unplanned reboot etc. (SQL2008R2)

[Adam Wenger]

@Paul If you made your own question regarding this, there may be more information we could get about your problem, and potentially come up with a solutions - working things out in the comments isn't always easy.

[Paul]

I just felt when I saw something wrong with this answer I should say something, for future people not to take it absolutely at face. I've already had to move on from this subject for now.

[Paul]

dba.stackexchange.com/questions/63349/…

[alastairtree]

This includes jobs that have started but did not finish because they errored. EXEC msdb.dbo.sp_help_job works better

[MarmiK]

@Paul use session_ID to overcome the older session values.. using sysjobhistory you can check if job failed then we can avoid that results too.

[Simon Tewsi]

@Paul: I believe the following StackOverflow answer might do the trick for you: stackoverflow.com/a/18062236/216440 It only includes activity from the current SQL Agent session. See also stackoverflow.com/a/13038752/216440 for an explanation of how activity records from previous sessions might be getting stuck.

[user1328350]

Not the right answer, because this lists running jobs too.

[Niraj]

I got a better answer here dba.stackexchange.com/questions/58859/…, see answer by Kenneth Fisher which returns only currently running jobs

[Simon Tewsi]

One important feature of this script is that it only selects jobs running in the current SQL Agent session. Jobs that were running when a previous SQL Agent session ended will also have a NULL stop_execution_date. We want to exclude those, hence the ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)

[colbybhearn]

@AdamWenger My VM with SQL Server lost power while running a job. JobActivity has a NULL for stop_execution_date as a result, despite many other instance of the job completing successfully since that outage.

[JonoB]

Should probably leave this answer as a link to the original answer rather than copy the code because there's a useful comment highlighting a limitation: 'Unfortunately, this script assumes that the currently running step is the one after the last completed step. This isn't always the case'

[Golden Lion]

how can I find errors that may have occurred while the job is running?

[Daxesh Radadiya]

@GoldenLion, right-click on the job you ran and select job history. Then expand the failed job log. You can scroll down and see the reason for failure.