1

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?

Community
  • 1
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320

2 Answers2

8

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
AA.SC
  • 4,073
  • 4
  • 28
  • 45
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.

Lee
  • 11
  • 1