January 14, 2009 at 10:18 am
Using SQL server 2005, I can look at the job activity monitor, and see the job name and Last Run date and last run outcome. Is this information available in one of the system databases and how can I query it?
I have 3 jobs that run at night and they're all dependent on each other (job 2 depends on job 1 running first, job 3 depends on job2 running first, etc). Then reports are run against job3.
I was thinking that if I could query the date the jobs ran and "succeeded" or "failed", then I could inform the users of the most available data.
Any help is greatly appreciated, thanks!
January 14, 2009 at 10:40 am
If your job dependencies are all linear, Job 1 then 2 then 3, you could add sp_start_job as the last step of jobs 1 and 2 to call 2 and 3, respectively.
You can query the job history using sysjobs and sysjobhistory in msdb.
SELECT
j.NAME AS job_name
,CASE run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In Progress'
ELSE ''
END AS run_description
-- ,message
,h.run_date
,h.run_time
FROM
msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id AND h.step_id = 0
--WHERE
-- j.NAME = 'YourJob'
January 14, 2009 at 11:13 am
awesome!! Thank you so much. 2 quick questions: I also added the "run_duration" column, but is that in minutes or seconds?
Also, while our processes are linear, they actually occur on 2 different servers.
The first 2 servers are in a different location, one is the production db. Each night it restores a copy to a 2nd database for reporting purposes.
However, since this 2nd db is in a different physical location and connected via a VPN, we run 2 SSIS packages to copy the data we need to our local sql server in our offices for reports, so that they run faster on the local server.
Is there a way to make this 2nd job dependent on the first one completeing first? Where it will wait or re-check to see if the 1st job was successful?
Thanks again!
January 14, 2009 at 11:36 am
You can make the last step of a job be something that logs completion, and the first step of the next job be something that checks for that, and waits if it gets a negative response.
It might, however, be easier to consolidate your actions into a single job that is managed by one or the other server, and just has multiple steps in it. Then you can control the flow completely. Would that be possible in your case?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2009 at 3:37 pm
Yes, thank you all very much for the help, I think I'm going to try to set job#1 to kick off job #2 when finished with sp_job_start. The only think is that they're linked servers connected via a VPN, they're not on the same network.
When connecting the linked servers together, all server#1 would need to do is execute the sp_job_start task. What's the least amount of privildges I would need to all an account access to do this?
Thanks again!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply