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.
| 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 perthe 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
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 |
|
|
|
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. |
|
|
|
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 |
|
|
|
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. |
|
|
|
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 |
|
|
|
|
|
|
|
|
SQL Agent Job Done and Not Done