Home Forums SQL Server 2005 Administering sysJobActivity showing jobs from years ago that never ended RE: sysJobActivity showing jobs from years ago that never ended

  • I was having this same problem, and it turns out the key is the the session_id column in msdb..sysjobactivity. To answer the OP's questions:

    (1) In my case, these "orphaned" records in sysjobactivity were caused by server restarts that occurred before the jobs had a chance to finish. You may be able to prevent this by stopping the SQL Agent service manually before restarting, but I haven't tested that.

    (2) I'm not sure whether it's safe to update this table manually, so I wouldn't recommend it.

    (3) Rather than "cleaning up" these records, I found that joining sysjobactivity with syssessions solved my issue. Each time SQL Agent starts, it writes a record to msdb.dbo.syssessions, so we're only interested in the sysjobactivity rows where session_id equals the max(session_id) from syssessions. This essentially limits your result set to the job activity since the last server restart.

    For example, I have a Change Data Capture job that needs to run all the time, so I have an alert that emails me if the following query returns 0:

    DECLARE @currentSession AS INT

    SELECT @currentSession = MAX(session_id)

    FROM msdb.dbo.syssessions

    SELECT count(*)

    FROM msdb.dbo.sysjobactivity ja

    JOIN msdb.dbo.sysjobs j

    ON j.job_id = ja.job_id

    WHERE j.name = 'cdc.Audit_capture'

    AND session_id = @currentSession

    AND ja.run_requested_date IS NOT NULL

    AND ja.stop_execution_date IS NULL