Share via

SQL Job not running but Job History showing in executing status

  • Article

Question

Monday, January 11, 2016 8:58 PM

Hi All,

We got a server restart on the server, later some time we had checked for one SQL Job currently the job is not running but in SQL Job history the job is showing in execution status.

While tried to stop manually the "Stop Job" was grayed out.

Is any one faced like this scenario.

Thanks,

Satish Kumar.

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

All replies (6)

Tuesday, January 12, 2016 12:40 AM

Check using query :-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 NULLReferhttp://dba.stackexchange.com/questions/83469/job-showing-the-running-sign-but-not-currently-running

Please click Mark As Answer if my post helped.

Tuesday, January 12, 2016 7:41 AM

This might be due to unexpected restart of SQL Server and hence SQL Server agent.

What does below retrun when you try to stop job

USE msdb ;
GO

EXEC dbo.sp_stop_job
    N'Job_name' ;
GO

Can you check using TSQL if this job is realy running. Also look at activity monitor is this job is running ?

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;

If nothing helps you might try to restart SQL Server agent.

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My Wiki Articles
MVP

Tuesday, January 12, 2016 8:48 PM

HI Shanky,

I was already tried both the options, the SQL Job was not running even though tried to stop the job with sp_stop_job before posting here.

From Job Activity Monitor the job is IDLE, for the same job in "Job View History" -- it is showing in executing status.

I know this is exceptional case and happened due to Server Restart., I m thinking about if restart of Agent / SQL Services fix the issue?

Thanks,

Satish Kumar.

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Wednesday, September 12, 2018 3:18 AM

im havin same issue , whats the solution ?

Monday, February 3, 2020 3:45 PM

After a inespered restart of machine, I had the same problem.
I tried stop and restart the agente job, but without sucess.
The unique option the I can found was drop and recreate the job.

If there is another solution, please tell to us!

Monday, February 3, 2020 3:47 PM

you can check SQL log might be it helpful 

Biren