What does it mean to have jobs with a null stop date?

Question

I had used the following SQL to list out all jobs without a stop date. I thought that I could use this to find all active jobs. What I noticed is that I have a number of jobs in this table with a null stop_execution_date. Some identical jobs (same job_id) are repeated multiple times in this table.

select job.*, activity.*
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity
on (job.job_id = activity.job_id)
where run_Requested_date is not null and stop_execution_date is null

When I run EXEC msdb.dbo.sp_help_job on these jobs, I see that they the current execution status is idle.

What do these jobs represent? Is this the behavior when the jobs are not killed properly?

Manutius

Answer 1

Each time the SQL Agent starts, it puts a new row in syssessions and subsequently any jobs run will get that session_id in sysjobactivity. For your jobs that have a null stop date, my guess is that they're not for the "current" session which would mean that they were still running when the agent was stopped.

Malynda

I agree with the comment about syssessions -- it is necessary to determine which jobs are currently running from sysjobactivity. However, a NULL stop date in the current session means the job is currently running. – Limitative

In the original question, the poster said that the sp_help_job showed the jobs as idle. But you're right, that is another case in which the end date would be NULL. – Malynda