1

I have a SQL Agent Job that shows as idle in the Activity Monitor but the time duration keeps increasing.

The job seems to have stopped as I've tried stopping it manually and SQL advises the job isn't running.

SysJobActivity doesn't have a stop_execution_date for the job

The job has 5 steps and the last step didn't complete - the server rebooted during the execution of this step.

Is the job ok to leave in it's current state? The duration will forever keep increasing.

Thanks

Job History

Activity Monitor

Job History - Updated

Brian
  • 83
  • 12
  • Something sounds wonky. Look into doing `sp_who` and see if you can find the `pid` that is running. Then you can `kill` it. If the duration is going up I would think it is running, I would _not_ just leave it alone. Sometimes Microsoft SQL Management Studio (the GUI) will not be .. _accurate_. Let me know what you find out. – sniperd Jul 25 '18 at 13:30
  • Can you post some screenshot of it? Sounds like if the job says its stopped then something might need to be killed - Are you sure the DB is not in recovery mode? – SqlKindaGuy Jul 25 '18 at 13:35
  • Screenshot of history added. sp_who doesn't show that job as running – Brian Jul 25 '18 at 13:58
  • Ah, OK. If there is nothing in `sp_who` I would 100% trust that over anything I see in the `GUI`. My guess is due to the reboot and there is no `stop` and something on the report side is just wrong. Could be a bug with MSSQL! Are you able to simply reboot the entire box or bounce the service to see what happens? – sniperd Jul 25 '18 at 14:51
  • See what is in here too: `USE msdb SELECT * FROM msdb..sysjobhistory` I think the GUI just reports out of here. – sniperd Jul 25 '18 at 14:56

1 Answers1

0

After the comments and viewing the screenshots I think I know what is going on:

  • The job duration is reported by what is in the msdb..sysjobhistory
  • The reboot during the job caused a problem (perhaps the power was killed to the box so it couldn't log properly?) so the job never really failed or finished and not properly recorded into the sysjobhistory.
  • It's not showing up in sp_who which means it is NOT running

I suspect it's probably OK to leave the job just 'running' forever. But I would suggest clearing that up so some other poor DBA isn't scratching his head. You could:

  • Manually edit the msdb..sysjobhistory which is scary and I wouldn't.
  • I bet start and stop the job and now it will report OK
  • Delete the job and history via the GUI and remake it (script it out first!)
sniperd
  • 5,124
  • 6
  • 28
  • 44
  • Ok great - I'll see if the job runs tomorrow. I won't take any action with the job itself (update syshistory etc.) Fingers crossed all is ok in the morning! Thanks again! – Brian Jul 25 '18 at 15:39
  • Cool, let me know how it turns out. Now I'm curious :) – sniperd Jul 25 '18 at 17:41
  • I've added a new screenshot :) The job that was running for 12+ hours has disappeared and the it's been replaced with this morning's job! Strangely yesterday's job steps have been moved into this morning's job. – Brian Jul 26 '18 at 13:07
  • @Brian OK, that all makes sense now. For whatever reason (the sudden reboot) the previous run never put it's `stop` or `end` or `fail` or `success` into the `msdb..sysjobhistory` so it just thinks this job has 2 starts and 1 stop and that's it. You could `SELECT` out of that table to see if you are interested. That tells me you have nothing to worry about, the endless duration didn't mean it was still running. I strongly suspect tomorrow the job reporting will back to normal. Mystery solved! If you've found my answer helpful, please accept it :) – sniperd Jul 26 '18 at 13:16