September 2, 2021 at 9:10 am
(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.
September 2, 2021 at 9:38 am
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.
March 29, 2023 at 11:52 pm
It's crazy that this is still relevant in 2023.
I just want to see if these were your steps in resolving this.
June 29, 2023 at 2:20 pm
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