sysJobActivity showing jobs from years ago that never ended

  • (1) What might have caused this? Because your query is wrong. In this table you have all the job logs from the beginning but you want to consider the jobs since SQL Agent started. Or what its the same: the last session. Add to your query the following filter:

    activity.sysjobactivity.session_id = (
    SELECT
    MAX(session_id)
    FROM msdb.dbo.sysjobactivity
    )

    (2) Is it safe to just do an UPDATE on these records and change the stop_execution_date to something like '1900-01-01'.

    It's not safe. You should never change a system table. In this case, when a job finishes, a record in the job history is generated. So, please don't do it

    (3) Are there any better ways to tidy this up? Filtering by the last session of the SQL Server Agent Service.

  • Wow, this a blast from the past! Don't think I've ever had a reply to a post as old as 8 years before!

    Thanks for the response, but this has long since been resolved lol.

    IIRC, it wasn't my query that was causing the problem, SQL Agent was reporting never-ending jobs, meaning the next scheduled run could not begin. Further investigation prompted this post, so my query was only showing what the SQL Agent itself was using.

    After much reading and testing, the decision was made to delete/modify certain job history/activity records, which in itself didn't resolve the issue, but then following it with an agent restart, everything ended up working again. Restarting the Agent by itself did not work, records did have to be manually adjusted.

    BTW, your blanket advice to never adjust system objects is great for newcomers to SQL, but glitches and bugs do happen, and sometimes you need to get your hands dirty.

    • This reply was modified 3 years, 1 month ago by  RossRoss.
  • It's crazy that this is still relevant in 2023.

    I just want to see if these were your steps in resolving this.

    1. Modify the sysjobactivity table

      1. I'm assuming this just means adding a value to last_executed_step_id and a stop_execution_date?

    2. Modify the sysjobhistory table

      1. Add an entry? I'm not certain how the two tables correlate beyond how sysjobactivity having a history_id column so I'm not sure on the reasoning on why this was stated in your post.

    3. Restart SQL Agent

      1. Because it flushes the finished jobs from the activity table?

  • This was removed by the editor as SPAM

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply