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

  • Thanks TheJrDBA, that's a very useful solution.

    I tried something else which seems to work on my dev server to actually remove the jobs from any reports that show long runners, time will tell how it plays out though:

    UPDATE msdb.dbo.sysjobactivity

    SETlast_executed_step_id = 1,

    last_executed_step_date = '2014-02-18',--Today or any date

    stop_execution_date = '2014-02-18' --Today or any date

    WHEREsession_id = 2006--SessionID of the problematic instances of job

    ANDjob_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'--jobID of the problematic job

    I've only tried this on the dev server, it does have jobs running frequently though, and I monitor them with Redgate SQL Backup, SQLJobVis, and my own queries, for all intensive purposes, the jobs that were showing as never finishing are showing as complete now. That said, I haven't updated the problem records with a job_history_id or a next_sheduled_run_date but I don't think it needs these to just show the jobs as complete for the sake of reporting.

    I'll keep an eye on how things play out before trying anything like this on a production server, and I'll post back here as/when I come to any conclusions about it.