SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Agent Job Done and Not Done
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1643 Posts

Posted - 12/11/2013 :  15:10:56  Show Profile  Reply with Quote
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

USA
36613 Posts

Posted - 12/11/2013 :  15:14:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1643 Posts

Posted - 12/11/2013 :  16:50:00  Show Profile  Reply with Quote
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

USA
790 Posts

Posted - 12/12/2013 :  13:12:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1643 Posts

Posted - 12/12/2013 :  16:38:49  Show Profile  Reply with Quote
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

Edited by - Bustaz Kool on 12/12/2013 16:39:23
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
790 Posts

Posted - 12/13/2013 :  17:50:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1643 Posts

Posted - 12/16/2013 :  19:47:56  Show Profile  Reply with Quote
Cool. Thanx!

=================================================
No, no, you're not thinking, you're just being logical. -Niels Bohr
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000