Controlling Unusually Long Running Jobs

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/controllingunusuallylongrunningjobs.asp

  • SQLZ

    SSChampion

    Points: 12872

    Leo,

    good article.  However, there is an issue with your use of the sysjobhistory table.  You mention, near the end of your article, that if we want to check on jobs that are still running we should change the line:

    and soh.run_status = 1 -- successful jobs

    with line

    and ( soh.run_status = 1 or soh.run_status = 4 ) -- successful and in progress jobs

    However, the sysjobhistory table does not get populated by history information until the job completes, which means you cannot check for jobs that are still in progress.

    I would suggest using master..xp_sqlagent_enum_jobs instead, which will always give you a more up to date view of what is happening to your jobs.

    Regards,

  • James Beaty-208368

    SSC Rookie

    Points: 49

    I hope this is not too OT....

    Is xp_sendmail still a preferred method of sending out messages from SQL Server?  In some shops I have worked in xp_sendmail is not set up properly, Microsoft security prevents it from being sent (i.e. when you use the command it uses the pop-up confirmation screen), and/or the DBA manager does not want to use it due to a hacker or program gaining control of SQL Server and sending out mail.

    Maybe someone could enlighten us on the use of xp_sendmail and how to set it up properly and securely?

    I would love to use this script, but the use of xp_sendmail concerns me a bit and maybe someone can give us a few tips.

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    You can use any type of email. Check internet there are bunch of articles how to use CDO mail object. I just didn't focus on mail portion. Sorry.

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    I didn't check it. But I will. I was stating based on SQL Server Book on line.

    run_status int Status of the job execution:

    0 = Failed

    1 = Succeeded

    2 = Retry

    3 = Canceled

    4 = In progress

  • SQLZ

    SSChampion

    Points: 12872

    Yeah, I'm not sure what the problem is really.  There is a run_status field in sysjobhistory but it doesn't get populated until the job finishes, which defeats the purpose of having that field (other than for failed or succeeded values). 

    Perhaps BOL is in error, which wouldn't be the first time.

    Either way, let me know what you find when you check it.

     

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Will do it. It become interesting.

    I am in class for 3 days. But then I will make some experiments and let you know. Thanks for the remark. Sorry for the error (if it is)

  • Rafael H

    SSC Veteran

    Points: 273

    I cannot understand the BOL either.  they posted the statuses, but no rows are populated untill the end of the step.

    Any news on this?


    Kindest Regards,

    R

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Check my article http://www.sqlservercentral.com/columnists/lPeysakhovich/howtofindthatjobisrunning.asp

    It gives you some additions to the issue.

Viewing 9 posts - 1 through 9 (of 9 total)

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