Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to find if a job ran successfully?
Add to briefcase
24 posts, Page 1 of 3
123»»»

How to find if a job ran successfully? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
newdba_sql
Posted Wednesday, January 23, 2013 6:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 12:37 PM
Points: 12, Visits: 132
I have a sql Job B dependant on Job A. Job B should run only if the job B completed successfully. How can I add a step in Job B to check if the Job A ran and completed successfully? And if not then check it again after some time the status of the job until the job A succeeds.
Post #1410536
Nils Gustav Stråbø
Posted Wednesday, January 23, 2013 7:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 1,788, Visits: 3,327
Why not merge the step(s) in job B into job A? If the original steps in job A fails, the you quit the job, otherwise you continue to the steps from job B.
Post #1410555
newdba_sql
Posted Wednesday, January 23, 2013 8:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 12:37 PM
Points: 12, Visits: 132
I can't have both jobs running togather. Job doesnt need to run every time the Job A runs. Also Job A completes on different times but Job B should run on a sprcific time when it sees the last run ststus of job A completed successfully.
Post #1410620
Perry Whittle
Posted Wednesday, January 23, 2013 8:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:53 AM
Points: 5,204, Visits: 11,158 
select	j.name
	, convert(smalldatetime, convert(varchar(20), max(jh.run_date)))		
	, CASE jh.run_status
	WHEN 0 THEN 'Failed'
	WHEN 1 THEN 'Success'
	WHEN 2 THEN 'Retry'
	WHEN 3 THEN 'Cancelled'
	END AS Run_Status
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobhistory jh on
j.job_id = jh.job_id
where j.name = 'your job name'
group by j.name, jh.run_status

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1410649
ScottPletcher
Posted Wednesday, January 23, 2013 10:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778
That's trickier than it sounds, because of the way SQL Server updates the sysjob* tables, and because you don't want to have to hardcode job schedules into the code.

Instead of relying on sysjob* tables, I'd suggest something along these lines:

the first action "Job A" takes is to update a row in a control table with a current datetime and a status that means "Job A has started but it has not fininshed yet";
the last action "Job A" takes is to set that control status to "Job A has finished successfully.";
if "Job A" fails it set that status accordingly.

Then "Job B" evaluates the "Job A" status:
A) if "Job A" was successful since "Job B" last ran, "Job B" can update its own separate datetime and status and run normally.
B) if "Job A" failed or didn't run,
"Job B" can update its datetime and/or status and exit (or whatever you want to do)
C) if "Job A" is still running, "Job B" can issue a WAITFOR and try again later; add a counter to limit the number of tries

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1410709
Perry Whittle
Posted Wednesday, January 23, 2013 11:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:53 AM
Points: 5,204, Visits: 11,158
and all that's easier than a quick check in the dbo.sysjobhistory table??

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1410715
MyDoggieJessie
Posted Wednesday, January 23, 2013 11:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:19 PM
Points: 2,037, Visits: 3,761
Since you want to check the current status of the job, I'd use sysjobactivity. Perhaps something like this?
WHILE (
(SELECT TOP 1 ISNULL(last_executed_step_id, 0)
	FROM msdb.dbo.sysjobactivity 
	WHERE job_id = 'UID'
	ORDER BY run_requested_date DESC ) = 0)
	BEGIN
		WAITFOR DELAY '00:05:00' -->> Recheck after a specified period of time
	END

	--> Run what should run if the job above isn't currently executing
	PRINT 'Good to go!'

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1410721
ScottPletcher
Posted Wednesday, January 23, 2013 11:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778
Perry Whittle (1/23/2013)
and all that's easier than a quick check in the dbo.sysjobhistory table??



Yes: which specific entry for "Job A" does one check in sysjobhistory?

The last one? What if that's from the previous run of "job a", and "job a" has not run again since "job b" last ran?

I think it will be easier to quickly determine the relative status of job "a" vs job "b" with specific flags for each, rather than relying on assumptions about each job.

I guess I'm willing to assume, as you clearly must, that msdb.dbo.sysjobhistory is not accidentally cleared between "a" and "b"; although it may be more possible than you think, depending on the number of job failures/retries, delays, and the (default) "remove agent history" settings of the SQL Server Agent.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1410727
DBA_Dom
Posted Wednesday, January 23, 2013 3:22 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:52 AM
Points: 563, Visits: 59,060
You could also have the last step in the job add a message in a queue for the service broker to pick up and execute the next job.

Or what i have done is have self maintaining schedules for a job. If it was sucessful it recesudules itself monthly (if it is not already on the monthly schedule). If it is not sucessful it changes its schedule to be weekly to try next week (there is a lot more decisions and complexity to it). This can also be translated into the idea of having your schedule for Job B disabled. At the last step of Job A it enables the schedule for Job B and then at the last step (probably to be reached regardless of sucess or fail) you disable the schedule again for Job B.

There are many different types/ways you can accomplish this from adding new schedules to on the fly create a new one time, delete after run job that is created by the sucessfull job A to be executed at a specific time/date.
Post #1410814
ScottPletcher
Posted Wednesday, January 23, 2013 3:41 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778
DBA_Dom (1/23/2013)
You could also have the last step in the job add a message in a queue for the service broker to pick up and execute the next job.

Or what i have done is have self maintaining schedules for a job. If it was sucessful it recesudules itself monthly (if it is not already on the monthly schedule). If it is not sucessful it changes its schedule to be weekly to try next week (there is a lot more decisions and complexity to it). This can also be translated into the idea of having your schedule for Job B disabled. At the last step of Job A it enables the schedule for Job B and then at the last step (probably to be reached regardless of sucess or fail) you disable the schedule again for Job B.

There are many different types/ways you can accomplish this from adding new schedules to on the fly create a new one time, delete after run job that is created by the sucessfull job A to be executed at a specific time/date.



What happens if you want to start running "Job A" on three different schedules, depending on the day?

Also, again, it seems to me as if that requires you to hard-code the job details into the job -- to me, that's a very bad idea and will inevitably cause you errors and other grief in the future.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1410820
« Prev Topic | Next Topic »

Add to briefcase
24 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.