SQL Job not running but Job History showing in executing status
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
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