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.

Ithunn

Job Activity Monitor in the SQL Server Agent section of SSMS. – Defecate

Answer 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

Brewage

This reports some non-running jobs on my servers. – Headliner

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) – Headliner

@Headliner 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. – Brewage

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. – Headliner

dba.stackexchange.com/questions/63349/… – Headliner

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

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

@Paul: I believe the following StackOverflow answer might do the trick for you: https://mcmap.net/q/260714/-how-can-i-determine-the-status-of-a-job It only includes activity from the current SQL Agent session. See also https://mcmap.net/q/260715/-what-does-it-mean-to-have-jobs-with-a-null-stop-date for an explanation of how activity records from previous sessions might be getting stuck. – Nirvana

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

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

@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. – Rattray

Answer 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.

Apartheid

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) – Nirvana

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' – Susann

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

@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. – Merrygoround

Answer 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

Interline

Answer 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).

Sugary

Answer 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
        ,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

Readjust

Answer 6

EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''

Notice the column Running, obviously 1 means that it is currently running, and [Current Step]. This returns job_id to you, so you'll need to look these up, e.g.:

SELECT top 100 *
 FROM   msdb..sysjobs
 WHERE  job_id IN (0x9DAD1B38EB345D449EAFA5C5BFDC0E45, 0xC00A0A67D109B14897DD3DFD25A50B80, 0xC92C66C66E391345AE7E731BFA68C668)

Labour

Answer 7

DECLARE @StepCount INT
SELECT @StepCount = COUNT(1)
FROM msdb.dbo.sysjobsteps
WHERE job_id = '0523333-5C24-1526-8391-AA84749345666' --JobID


SELECT
         [JobName]
        ,[JobStepID]
        ,[JobStepName]
        ,[JobStepStatus]
        ,[RunDateTime]
        ,[RunDuration]
    FROM
    (
        SELECT 
                j.[name] AS [JobName]
            ,Jh.[step_id] AS [JobStepID]
            ,jh.[step_name] AS [JobStepName]
            ,CASE 
                WHEN jh.[run_status] = 0 THEN 'Failed'
                WHEN jh.[run_status] = 1 THEN 'Succeeded'
                WHEN jh.[run_status] = 2 THEN 'Retry (step only)'
                WHEN jh.[run_status] = 3 THEN 'Canceled'
                WHEN jh.[run_status] = 4 THEN 'In-progress message'
                WHEN jh.[run_status] = 5 THEN 'Unknown'
                ELSE 'N/A'
                END AS [JobStepStatus]
            ,msdb.dbo.agent_datetime(run_date, run_time) AS [RunDateTime]
            ,CAST(jh.[run_duration]/10000 AS VARCHAR)  + ':' + CAST(jh.[run_duration]/100%100 AS VARCHAR) + ':' + CAST(jh.[run_duration]%100 AS VARCHAR) AS [RunDuration]
            ,ROW_NUMBER() OVER 
            (
                PARTITION BY jh.[run_date]
                ORDER BY jh.[run_date] DESC, jh.[run_time] DESC
            ) AS [RowNumber]
        FROM 
            msdb.[dbo].[sysjobhistory] jh
            INNER JOIN msdb.[dbo].[sysjobs] j
                ON jh.[job_id] = j.[job_id]
        WHERE 
            j.[name] = 'ProcessCubes' --Job Name
            AND jh.[step_id] > 0
            AND CAST(RTRIM(run_date) AS DATE) = CAST(GETDATE() AS DATE) --Current Date
    ) A
    WHERE 
        [RowNumber] <= @StepCount
        AND [JobStepStatus] = 'Failed'

Hoem

Answer 8

We've found and have been using this code for a good solution. This code will start a job, and monitor it, killing the job automatically if it exceeds a time limit.

/****************************************************************
--This SQL will take a list of SQL Agent jobs (names must match),
--start them so they're all running together, and then
--monitor them, not quitting until all jobs have completed.
--
--In essence, it's an SQL "watchdog" loop to start and monitor SQL Agent Jobs
--
--Code from http://cc.davelozinski.com/code/sql-watchdog-loop-start-monitor-sql-agent-jobs
--
****************************************************************/
SET NOCOUNT ON 

-------- BEGIN ITEMS THAT NEED TO BE CONFIGURED --------

--The amount of time to wait before checking again 
--to see if the jobs are still running.
--Should be in hh:mm:ss format. 
DECLARE @WaitDelay VARCHAR(8) = '00:00:20'

--Job timeout. Eg, if the jobs are running longer than this, kill them.
DECLARE @TimeoutMinutes INT = 240

DECLARE @JobsToRunTable TABLE
(
    JobName NVARCHAR(128) NOT NULL,
    JobID UNIQUEIDENTIFIER NULL,
    Running INT NULL
)

--Insert the names of the SQL jobs here. Last two values should always be NULL at this point.
--Names need to match exactly, so best to copy/paste from the SQL Server Agent job name.
INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES ('NameOfFirstSQLAgentJobToRun',NULL,NULL)
INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES ('NameOfSecondSQLAgentJobToRun',NULL,NULL)
INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES ('NameOfXSQLAgentJobToRun',NULL,NULL)

-------- NOTHING FROM HERE DOWN SHOULD NEED TO BE CONFIGURED --------

DECLARE @ExecutionStatusTable TABLE
(
    JobID UNIQUEIDENTIFIER PRIMARY KEY, -- Job ID which will be a guid
    LastRunDate INT, LastRunTime INT, -- Last run date and time
    NextRunDate INT, NextRunTime INT, -- Next run date and time
    NextRunScheduleID INT, -- an internal schedule id
    RequestedToRun INT, RequestSource INT, RequestSourceID VARCHAR(128),
    Running INT,    -- 0 or 1, 1 means the job is executing
    CurrentStep INT, -- which step is running
    CurrentRetryAttempt INT, -- retry attempt
    JobState INT -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread,
                     -- 3 = Between Retries, 4 = Idle, 5 = Suspended, 
                     -- 6 = WaitingForStepToFinish, 7 = PerformingCompletionActions
)

DECLARE @JobNameToRun NVARCHAR(128) = NULL
DECLARE @IsJobRunning BIT = 1
DECLARE @AreJobsRunning BIT = 1
DECLARE @job_owner sysname = SUSER_SNAME()
DECLARE @JobID UNIQUEIDENTIFIER = null
DECLARE @StartDateTime DATETIME = GETDATE()
DECLARE @CurrentDateTime DATETIME = null
DECLARE @ExecutionStatus INT = 0
DECLARE @MaxTimeExceeded BIT = 0

--Loop through and start every job
DECLARE dbCursor CURSOR FOR SELECT JobName FROM @JobsToRunTable
OPEN dbCursor FETCH NEXT FROM dbCursor INTO @JobNameToRun
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC [msdb].[dbo].sp_start_job @JobNameToRun
    FETCH NEXT FROM dbCursor INTO @JobNameToRun
END
CLOSE dbCursor
DEALLOCATE dbCursor

print '*****************************************************************'
print 'Jobs started. ' + CAST(@StartDateTime as varchar)
print '*****************************************************************'

--Debug (if needed)
--SELECT * FROM @JobsToRunTable

WHILE 1=1 AND @AreJobsRunning = 1
BEGIN

    --This has to be first with the delay to make sure the jobs
    --have time to actually start up and are recognized as 'running'
    WAITFOR DELAY @WaitDelay 

    --Reset for each loop iteration
    SET @AreJobsRunning = 0

    --Get the currently executing jobs by our user name
    INSERT INTO @ExecutionStatusTable
    EXECUTE [master].[dbo].xp_sqlagent_enum_jobs 1, @job_owner

    --Debug (if needed)
    --SELECT 'ExecutionStatusTable', * FROM @ExecutionStatusTable

    --select every job to see if it's running
    DECLARE dbCursor CURSOR FOR 
        SELECT x.[Running], x.[JobID], sj.name 
        FROM @ExecutionStatusTable x 
        INNER JOIN [msdb].[dbo].sysjobs sj ON sj.job_id = x.JobID
        INNER JOIN @JobsToRunTable jtr on sj.name = jtr.JobName
    OPEN dbCursor FETCH NEXT FROM dbCursor INTO @IsJobRunning, @JobID, @JobNameToRun

    --Debug (if needed)
    --SELECT x.[Running], x.[JobID], sj.name 
    --  FROM @ExecutionStatusTable x 
    --  INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = x.JobID
    --  INNER JOIN @JobsToRunTable jtr on sj.name = jtr.JobName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        --bitwise operation to see if the loop should continue
        SET @AreJobsRunning = @AreJobsRunning | @IsJobRunning

        UPDATE @JobsToRunTable
        SET Running = @IsJobRunning, JobID = @JobID
        WHERE JobName = @JobNameToRun

        --Debug (if needed)
        --SELECT 'JobsToRun', * FROM @JobsToRunTable

        SET @CurrentDateTime=GETDATE()

        IF @IsJobRunning = 1
        BEGIN -- Job is running or finishing (not idle)

            IF DATEDIFF(mi, @StartDateTime, @CurrentDateTime) > @TimeoutMinutes
            BEGIN     
                print '*****************************************************************'
                print @JobNameToRun + ' exceeded timeout limit of ' + @TimeoutMinutes + ' minutes. Stopping.'
                --Stop the job
                EXEC [msdb].[dbo].sp_stop_job @job_name = @JobNameToRun
            END
            ELSE
            BEGIN
                print @JobNameToRun + ' running for ' + CONVERT(VARCHAR(25),DATEDIFF(mi, @StartDateTime, @CurrentDateTime)) + ' minute(s).'
            END
        END

        IF @IsJobRunning = 0 
        BEGIN
            --Job isn't running
            print '*****************************************************************'
            print @JobNameToRun + ' completed or did not run. ' + CAST(@CurrentDateTime as VARCHAR)
        END

        FETCH NEXT FROM dbCursor INTO @IsJobRunning, @JobID, @JobNameToRun

    END -- WHILE @@FETCH_STATUS = 0
    CLOSE dbCursor
    DEALLOCATE dbCursor

    --Clear out the table for the next loop iteration
    DELETE FROM @ExecutionStatusTable

    print '*****************************************************************'

END -- WHILE 1=1 AND @AreJobsRunning = 1

SET @CurrentDateTime = GETDATE()
print 'Finished at ' + CAST(@CurrentDateTime as varchar)
print CONVERT(VARCHAR(25),DATEDIFF(mi, @StartDateTime, @CurrentDateTime)) + ' minutes total run time.'

Eaton

Thanks! This worked perfectly for us with some minor adjustments. – Laughton

Watch out for collation conflicts between "Latin1_General_CI_AI" and "Latin1_General_CI_AS" in the equal to operation. – Northeastwards

Answer 9

You can query the table msdb.dbo.sysjobactivity to determine if the job is currently running.

Puberty

this answer is incomplete or wrong. it doesn't say which column and information to look for in msdb.dbo.sysjobactivity. please update answer. – Aceous

Answer 10

This command below will show you the current running jobs for the current day.

It joins the sysjobs_view, sysjobactivity, and syssessions tables to get the job name, job id, originating server, date the run was started/requested, stop_execution_date, last_executed_step_id, run_status, date diff from current date.

The value of stop_execution_date will usually be NULL if the job is still running.

    SELECT
    job.name,
    job.job_id,
    job.originating_server,
    activity.run_requested_date,
    activity.stop_execution_date,
    activity.last_executed_step_id,
    hist.run_status,
    DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) AS Elapsed
FROM msdb.dbo.sysjobs_view job
     JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
     JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id
     JOIN
     (
         SELECT
             MAX(agent_start_date) AS max_agent_start_date
         FROM msdb.dbo.syssessions
     ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date
     JOIN  msdb.dbo.sysjobhistory hist ON hist.job_id = activity.job_id 
WHERE
    1 = 1
    AND YEAR(activity.run_requested_date) = YEAR(GETDATE())
    AND MONTH(activity.run_requested_date) = MONTH(GETDATE())
    AND DAY(activity.run_requested_date) = DAY(GETDATE())
    AND activity.stop_execution_date IS NULL
ORDER BY activity.run_requested_date DESC;

If you want to get the status of a job, the command below shows a slightly modified query from the initial query.

Here, we are getting the run status of all currently started jobs. We can use the value of the run_status to determine the current status of a job, i.e failed, canceled, in progress, successful etc.

SELECT
    job.name,
    job.job_id,
    job.originating_server,
    activity.run_requested_date,
    activity.stop_execution_date,
    activity.last_executed_step_id,
    hist.run_status,
    DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) AS Elapsed,
    CASE
        WHEN hist.run_status = 0 THEN 'Failed'
        WHEN hist.run_status = 1 THEN 'Successful'
        WHEN hist.run_status = 2 THEN 'Retry'
        WHEN hist.run_status = 3 THEN 'Canceled'
        WHEN hist.run_status = 4 THEN 'In Progress'
    END AS run_status_description
FROM msdb.dbo.sysjobs_view job
     JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
     JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id
     JOIN
     (
         SELECT
             MAX(agent_start_date) AS max_agent_start_date
         FROM msdb.dbo.syssessions
     ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date
     JOIN  msdb.dbo.sysjobhistory hist ON hist.job_id = activity.job_id 
WHERE
    1 = 1
    AND YEAR(activity.run_requested_date) = YEAR(GETDATE())
    AND MONTH(activity.run_requested_date) = MONTH(GETDATE())
    AND DAY(activity.run_requested_date) = DAY(GETDATE())
    AND job.name = '<enter job name here>'
ORDER BY activity.run_requested_date DESC;

I hope this helps!

Pat

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community? – Girasol