Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62

    Unanswered: 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, it’s 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,

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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?

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    Thank you for all of the suggestions you have for me.
    I will try them one by one.

  4. #4
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Does the code contains loops or cursors?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by blindman
    Does the code contains loops or cursors?
    Code:
    while (1=1) 
    begin
       print 'probably'
    end

  7. #7
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    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

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Whatever other issues may be occurring, step #1 is to drop the cursors and use set-based operations.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Unless the jobs sends separate e-mails to employees, for example...
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    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.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ypal
    ...I already suggested to go trough code, and rewrite some sql - got rejection
    Blindman's principle of employement: Never work for people who aren't as smart as you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does that make you unemployed Blinddude?
    George
    Home | Blog

  13. #13
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by georgev
    Does that make you unemployed Blinddude?
    More like unemployable.

    hmscott
    Have you hugged your backup today?

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    that's how you guys have so much time to post.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #15
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •