SQLServerCentral / Discuss Content Posted by Leo Peysakhovich / Article Discussions / Article Discussions by Author / Controlling Unusually Long Running Jobs / Latest Posts

RE: Controlling Unusually Long Running Jobs

Leo Peysakhovich — Mon, 19 Dec 2005 21:05:00 GMT

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

It gives you some additions to the issue.

RE: Controlling Unusually Long Running Jobs

Rafael H — Mon, 19 Dec 2005 17:29:00 GMT

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?

RE: Controlling Unusually Long Running Jobs

Leo Peysakhovich — Mon, 09 May 2005 08:15:00 GMT

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)

RE: Controlling Unusually Long Running Jobs

SQLZ — Mon, 09 May 2005 07:48:00 GMT

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.

RE: Controlling Unusually Long Running Jobs

Leo Peysakhovich — Mon, 09 May 2005 06:52:00 GMT

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 = Failed1 = Succeeded2 = Retry3 = Canceled4 = In progress

RE: Controlling Unusually Long Running Jobs

Leo Peysakhovich — Mon, 09 May 2005 06:48:00 GMT

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.

RE: Controlling Unusually Long Running Jobs

James Beaty-208368 — Mon, 09 May 2005 06:33:00 GMT

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.

RE: Controlling Unusually Long Running Jobs

SQLZ — Mon, 09 May 2005 03:13:00 GMT

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,

Controlling Unusually Long Running Jobs

Leo Peysakhovich — Tue, 19 Apr 2005 20:50:00 GMT

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