April 19, 2005 at 8:50 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/controllingunusuallylongrunningjobs.asp
May 9, 2005 at 3:13 am
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,
May 9, 2005 at 6:33 am
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.
May 9, 2005 at 6:48 am
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.
May 9, 2005 at 6:52 am
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 |
May 9, 2005 at 7:48 am
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.
May 9, 2005 at 8:15 am
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)
December 19, 2005 at 5:29 pm
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?
R
December 19, 2005 at 9:05 pm
Check my article http://www.sqlservercentral.com/columnists/lPeysakhovich/howtofindthatjobisrunning.asp
It gives you some additions to the issue.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply