Post reply

How to monitor long running jobs that don''t finish running

  • September 19, 2007 at 8:18 pm

    #118622

    hi ,

    I have discovered that at times i have certain jobs that run for a couple of days without finishing , how can i check the duration of a running job , so that i can set up alerts to notify me of say all jobs that keep running for more than an hour or so.

    thanks

  • September 21, 2007 at 12:48 pm

    #735346

    There certainly must be a script around for this.

    Job history is maintained in the msdb database. sysjobhistory system table

  • September 21, 2007 at 7:59 pm

    #735416

    Ummm... "monitoring" is great... helps you identify crap code that takes days to run without finishing... but, unless you take the next step, you might as well not waste your time "monitoring".  The "next step" I'm talking about is talking with the AppDev manager and work closely (and nicely) with him/her to get the code rewritten so that it's both performance enabled and scalable.

    And, someone has to start doing some performance testing and some code reviews before that kind of ill-performing code is ever allowed near your database.

    Now, what kind of improvements am I talking about?  I've taken many an "8 hour job" and turned them into 10 minute runs just using some of the practices you'll find here on this forum.  Latest accomplishment was a job that took 24 hours and constant operator attention to dupe-check a lousy 52 million rows.  After getting done with the rewrite, it would dupe-check 78 million rows in only 22 minutes... and no operator attention required.

    Ya gotta be willing to go the extra mile or the only thing the monitoring will do is remind you that the code isn't up to snuff.  Sure, some index analysis may help (a lot, in some cases)... messing around with memory, location of files, and splitting TempDB to match the processors will all help... about 1 to 5%...

    What should you target?  Any form of RBAR including cursors, most While loops, triangular joins (can be thousands or even millions of times worse than a cursor... see the URL below), triggers, functions, any proc that processes just one row at a time, and non-indexed aggragate views.  (RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row").

    Here's the URL about triangular joins and correlated sub-queries... well worth the read, if I do say so myself

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=359124&p=2

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply