SQL Server – Handling Orphaned Entries in msdb..sysjobactivity

msdbsql serversql-server-2008-r2

In this question:
https://stackoverflow.com/questions/18445825/how-to-know-status-of-currently-running-jobs

This answer was proposed:

SELECT sj.name
   , sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
   AND sja.stop_execution_date IS NULL

However for one of my servers this has an entry dating back 10 months ago. Looking in SQL Agent Job Activity Monitor shows no running jobs. Looking in msdb..sysjobs shows a mere 3 jobs. Can anyone explain this phenomenon?

The orphaned row has NULLs for queued_date, stop_execution_date, job_history_id and next_scheduled_date. Rest of the values look reasonable.

Best Answer

A StackOverflow answer has a possible explanation:

https://stackoverflow.com/questions/13037668/what-does-it-mean-to-have-jobs-with-a-null-stop-date/13038752#13038752

To quote:

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.

I had a similar issue where two entries in sysjobactivity appeared to be "stuck", with a start_execution_date from six weeks back and no stop_execution_date. Following the suggestion in the quote above I checked the session_id for each of those stuck records and found in each case the stuck records were from previous sessions.

The following StackOverflow answer shows how to get only the activity for the current session:

https://stackoverflow.com/a/18062236/216440

Their code:

SELECT
    job.name, 
    job.job_id, 
    job.originating_server, 
    activity.run_requested_date, 
    DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) as Elapsed
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
Related Question