Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Controlling Unusually Long Running Jobs Expand / Collapse
Author
Message
Posted Tuesday, April 19, 2005 8:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/controllingunusuallylongrunningjobs.asp


Post #175914
Posted Monday, May 9, 2005 3:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 870, Visits: 897

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,



Karl
source control for SQL Server
Post #180783
Posted Monday, May 9, 2005 6:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 24, 2007 8:38 PM
Points: 5, Visits: 1

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.

Post #180803
Posted Monday, May 9, 2005 6:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272
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.


Post #180806
Posted Monday, May 9, 2005 6:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272

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

run_statusintStatus of the job execution:

0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress




Post #180808
Posted Monday, May 9, 2005 7:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 870, Visits: 897

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.

 



Karl
source control for SQL Server
Post #180822
Posted Monday, May 9, 2005 8:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272

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)




Post #180856
Posted Monday, December 19, 2005 5:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 11, 2014 4:08 PM
Points: 12, Visits: 129

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
Post #245405
Posted Monday, December 19, 2005 9:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272

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

It gives you some additions to the issue.




Post #245429
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse