6

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?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
stevebot
  • 23,275
  • 29
  • 119
  • 181

1 Answers1

6

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.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • 3
    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. – efesar Aug 05 '13 at 15:45
  • 2
    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. – Ben Thul Aug 05 '13 at 18:16