Tuesday, March 20, 2012

scheduled job runs forewer

Hello,
I have scheduled job that are scheduled to run every 2 hours.
Its exec stored procedures. Normal execution time is up to 10 min.
But recently job not running successfully, its just executes forever. So I had to kill that job and I run it manually.
Please help,
I have no idea why it stops executing on its own by the schedule.

Thank you,did you use sp_who2 to see if it is blocked?

did you use dbcc_inputbuffer or sql_handle to see if the code executing changed?

did you create an output file in the job scheduler to capture output from the job steps?

did you change the procs to write to the output file as each starts, runs, and ends?

did you logically think "what can i do to troubleshoot this issue?"

did you google for similar situations?

did you look at the "sticky" at the top of the forum to see what you might need to add to your post to help us help you?|||Thank you for all of the suggestions you have for me.
I will try them one by one.|||How about trying one of the easiest things, stop and start the sql agent. Sometimes the scheduler gets wacked out and a simple stop and start will fix it.|||Does the code contains loops or cursors?|||Does the code contains loops or cursors?

while (1=1)
begin
print 'probably'
end|||The procedure does contain cursors and it runs Ok via SQL Query Analyzer, but once I add it up to SQL Server Agent Job it takes forever...
I do have an output file to capture output from the job steps, but since the job never been completed, and I had to kill it, there are no completion info.
If I will stop and start the sql agent before execution of this job, how the other scheduled processes be affected by that?

Thank you|||Whatever other issues may be occurring, step #1 is to drop the cursors and use set-based operations.|||Unless the jobs sends separate e-mails to employees, for example...|||The procedure does send an e-mail to few employees. The proc was written way before I started my work within the company, I already suggested to go trough code, and rewrite some sql - got rejection, so not sure how else to handle the issues.
Thank you.|||...I already suggested to go trough code, and rewrite some sql - got rejectionBlindman's principle of employement: Never work for people who aren't as smart as you.|||Does that make you unemployed Blinddude? :p|||Does that make you unemployed Blinddude? :p

More like unemployable.;)

hmscott|||that's how you guys have so much time to post.|||your boss doesn't have to be as smart as you.

what's important is that he/she does what you tell them to do. :)

No comments:

Post a Comment