Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Agent Job Done and Not Done

Author  Topic 

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-12-11 : 15:10:56
I have a SQL Job to rebuild/reorganize indexes on a server. The job on every server has benn "running" for a few days per
the Job Activity Monitor. BUT, when I look at the sysjobhistory table, the Duration column indicates that the job completed in the normal time frame. The nexy (daily) run of the job won't kick off since it thinks the current instance is still running. Also, I used Profiler to see what the job might be hanging on (infinite loop, etc.) and nothing is executing. Does anyone have any insight as to why it is completed but still active? All thoughts, questions, suggestions are welcomed.

=================================================
No, no, you're not thinking, you're just being logical. -Niels Bohr

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-11 : 15:14:12
Sounds like the Agent is hung up in some way. Try restarting the Agent and see if it happens again.

The Agent does hang up for us on our clusters (various SQL and OS versions) and affects our backup jobs only. I am not sure why other jobs aren't impacted.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-12-11 : 16:50:00
Thanks for the feedback. It is just the one job but it is the same status on every server. Bizarre, at best.

=================================================
No, no, you're not thinking, you're just being logical. -Niels Bohr
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-12-12 : 13:12:04
If you update an agent jobs properties manually - the information for that job will not be updated correctly. SQL Server Agent caches the job information and only refreshes the job information when the cache is updated.

I have seen these kinds of issues when someone has written manual updates of the tables and not called the appropriate procedures to update the cache.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-12-12 : 16:38:49
We always use the system stored procedures or the SSMS front-end to create/change the jobs, schedules, alerts, etc. When you say "manually" update the job, do you mean acting directly on the system tables? Also, what procedure call would update the cache?

Thanks for the info.

=================================================
No, no, you're not thinking, you're just being logical. -Niels Bohr
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-12-13 : 17:50:26
Yes - I meant directly through the tables in msdb. There are stored procedures available to update jobs/jobsteps - like sp_update_jobs which when called also refreshes the agent cache by calling sp_agent_notify which in turn calls xp_agent_notify.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-12-16 : 19:47:56
Cool. Thanx!

=================================================
No, no, you're not thinking, you're just being logical. -Niels Bohr
Go to Top of Page
   

- Advertisement -