July 5, 2006 at 2:18 am
Hi
I have a job that has two schedules one that runs once a week and the other runs once a month. The monthly job should always run but the weekly job should not run if the monthly one ran in the last 3 days.
My questions are is there a way in T-SQL of identifying
1. which schedule initiated the job ?
2. When the last date the job ran and which schedule initiated it ?
Thanks
Andy
July 5, 2006 at 4:01 am
You can get that info from poking around in MSDB.
But it might be easier and the code clearer just to have table which the monthly proc writes to, specifying the run date.
Or you could just get the weekly proc to check what day of the month it is (assuming the monthly job runs on the same day each month), though this would introduce an implicit dependency between the weekly proc and the monthly job, which could cause problems if someone doesn't realise they are interdependent and changes one of them.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 5, 2006 at 4:20 am
Hi Andy,
You can query the sysjobservers table to get this information.
select *
from sysjobservers s
join sysjobs j on j.job_id = s.job_id
where j.name = ''
The message column contains the scheduleid of the schedule that started the job. Unfortunately it's contained in a string so you'll need to strip it out by looking for "The Job was invoked by Schedule".
This table also contains the start date and start time for the job. By the way, note that the last_run_outcome of 1=success (as opposed to what BOL might tell you). Also, the last_run_duration is not in seconds but in the following format hhmmss. Except, because it's an integer, it won't contain leading zeros. Again, BOL is wrong in this instance.
I'm not sure if schedule id is stored anywhere in its own column but I couldn't find it at first glance. xp_sqlagent_enum_jobs gives you the schedule id of the schedule that will next invoke the job (but not the last time it was invoked).
master..xp_sqlagent_enum_jobs 1,'dbo',0xD2ABB2540C57C74ABAF141A63E650D9D
Anway, hope that helps,
July 5, 2006 at 4:52 am
sysjobservers must have been a view on MSDB tables. I can't be sure because I haven't got a SQL8 instance to hand. See what I mean about it being easier to write to a table yourself, though?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply