0

I have a script to get the currently running jobs:

-- get the running jobs
--marcelo miorelli
-- 10-dec-2013
SELECT sj.name
,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
--AND sj.name = 'JobName'
and not exists( -- make sure this is the most recent run
    select 1
    from msdb..sysjobactivity new
    where new.job_id = aj.job_id
    and new.start_execution_date > aj.start_execution_date )

when I run that on my server it says that there is no job currently running, however, when I look at the Job Activity Monitor, I see the picture below.

The second step of this job, runs a powershell script using a proxy. you can see the second step here.

enter image description here any reason why it would show the second step of my job as running? how can I fix it?

  • 2
    You can fix it by not using the UI to verify the status of jobs/steps? – Aaron Bertrand Nov 24 '14 at 14:49
6

Use script instead to check status of currently running job.

SELECT  job.name,
        job.job_id,
        job.originating_server,
        activity.run_requested_date,
        DATEDIFF(MINUTE, activity.run_requested_date, GETDATE()) as Elapsed,
        case when activity.last_executed_step_id is null
             then 'Step 1 executing'
             else 'Step ' + convert(varchar(20), last_executed_step_id + 1)
                  + ' executing'
        end
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
WHERE   run_requested_date IS NOT NULL
        AND stop_execution_date IS NULL
1

In SQL Agent, I noticed in the job history of a Powershell job that some of the steps had spun off what appeared to be parallel job steps. I saw two rows for the same step: one of them displaying the simple green checkmark, the other step showing the "still running" green circle-wedge.

In my case, I figured out what was causing this...

Right-click the job in SQL Agent, navigate: Properties -> Steps -> Edit -> Advanced.

In my display, the bottom-most checkbox prompts: "Include step output in history"...

I unchecked it, and the next time it ran, the circle-wedge pseudo-step had disappeared from the job history.

  • Your suggestion to un-check "Include step output in history" did the trick for me. Thanks! – David Bunch Nov 17 '17 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.