October 3, 2010 at 6:34 pm
In SysServers we have the Last_Run_Date and Last_Run_Time.
How do I find out if the Last_Run_Date and Last_Run_Time were On Schedule.
Example I have a Job that runs every 2 Hours from 8:00 am to 8:00 pm Daily.
The Time now is 10/03/2010 5:30 PM
The Last Scheduled Run would have been at 10/03/2010 4:00 pm and
Next Scheduld Run would be 10/03/2010 6:00 pm
SysServers has Last_Run_Date an Last_Run_Time as 09/31/2010 8:00 am.
This is not the last expected sheduled run, it is 10/03/2010 4:00 pm.
Is there a Function or query to get this information
October 3, 2010 at 10:04 pm
You may find this script[/url] useful.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
October 3, 2010 at 10:55 pm
Thanks for sharing the Script, but I still cannot get the following Info.
(Assuming the time is 10/03/2010 5:30 PM)
a) SysServers has LastRun as 09/31/2010 8:00 am.
(Which is an old successfull run and the table did not get updated)
b) But the Expected Lastrun should have been 10/03/2010 4:00 pm.
Basically I need to make sure that the LastRun shown in SysServers is really the Expected LastRun, by using the schedule frequency and GetDate(). I was hoping there was a query.
This should be a typical DBA Query, since the DBA needs to know if the Job really ran on schedule programatically.
October 3, 2010 at 11:23 pm
Basically I need to make sure that the LastRun shown in SysServers is really the Expected LastRun, by using the schedule frequency and GetDate(). I was hoping there was a query.
This should be a typical DBA Query, since the DBA needs to know if the Job really ran on schedule programatically.
SysServers does not hold any information related to jobs. This query will list the job name and the last run details in descending order.
select b.name as JobName,a.run_requested_date from sysjobactivity a, sysjobs b
where a.job_id=b.job_id
order by run_requested_date desc
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
October 4, 2010 at 1:29 am
That's right. I had that in my query too.
select b.name as JobName,a.run_requested_date from sysjobactivity a, sysjobs b
where a.job_id=b.job_id
order by run_requested_date desc
But the Question is: How do I Positively Progamatically verify the run_requested_date was as per schedule.
Given the Current Date and Time, How do I tell Programatically the run_requested_date was as per schedule.
Here is the approach I am thinking, Get the NextRun DateAndTime and minus the time between Runs to get the last Run and verify a record exists in sysjobactivity. Please let me know if you have a query doing the same.
October 4, 2010 at 2:31 am
SQLCoder77 (10/4/2010)
Here is the approach I am thinking, Get the NextRun DateAndTime and minus the time between Runs to get the last Run and verify a record exists in sysjobactivity. Please let me know if you have a query doing the same.
OK, so you've worked out how you're going to do it, now all you need to do is write the code. Have a go at that and post again if there's anything in particular you're struggling with. I would recommend that you ditch the old-style join syntax and use the ANSI syntax instead.
By the way, are you sure you have the date 09/31/2010 in your table?
John
October 4, 2010 at 5:11 pm
Question: By the way, are you sure you have the date 09/31/2010 in your table?
Answer: Yes
October 4, 2010 at 8:05 pm
Is there a way to find out if a JobRan on Schedule Programatically.
[FYI: I am still looking for some help on this Question.]
October 5, 2010 at 1:20 am
Nobody's going to help you if you won't help yourself. Try writing the code, and post back if there is anything you're struggling with. Having said that, if you have dates such as 31st September in your tables then your reluctance to do any work yourself might be the smallest of your problems.
John
October 5, 2010 at 4:26 pm
Below is the Query:
How do I know if the LastRun was actually the Real Latest Scheduled Run that should have taken place?
-------------------------------------------------------------------------------------------------
Use MSDB
GO
WITH LastExpectedRun_CTE (Job_ID, Name, MaxSessionID)
AS
(
SELECT sj.Job_ID, sj.Name, max(session_id) as MaxSessionID
FROM msdb.dbo.sysjobs as sj
join sysjobactivity as sjo
on sj.Job_ID = sjo.Job_ID
group by sj.Job_ID, sj.Name
)
SELECT CTE.Job_ID, CTE.Name, max(session_id) as MaxSessionID, sja.next_scheduled_run_date,
Comment =
Case When sja.next_scheduled_run_date > Getdate() Then 'Next Run Date is in Future'
When sja.next_scheduled_run_date < Getdate() Then 'Next Run Date is in the Past'
End
into ##temp1
FROM LastExpectedRun_CTE as CTE
join sysjobactivity as sja
on (sja.Job_ID = CTE.Job_ID and sja.session_id = CTE.MaxSessionID)
group by CTE.Job_ID, CTE.Name, sja.next_scheduled_run_date
-------------------------------------------------------------------------------------------------
Use MSDB
GO
WITH LastRun_CTE(Job_ID, Name, MaxInstanceID)
AS
(
selectsj.Job_ID, sj.Name, max(instance_id) as MaxInstanceID
from msdb.dbo.sysjobs as sj
join sysjobhistory as sh
on sj.Job_ID = sh.Job_ID
group by sj.Job_ID, sj.Name
)
SELECT CTE2.Job_ID, CTE2.Name, max(Instance_ID) as MaxInstanceID,
CAST
(
CONVERT(VARCHAR, run_date)
+ ' ' + STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,run_time),6),5,0,':'),3,0,':')
AS DATETIME
) as LastRunDateTime into ##temp2
FROM LastRun_CTE as CTE2
join sysjobhistory as sh
on (sh.Job_ID = CTE2.Job_ID and sh.instance_id = CTE2.MaxInstanceID)
group by CTE2.Job_ID, CTE2.Name, CAST
(
CONVERT(VARCHAR, run_date)
+ ' ' + STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,run_time),6),5,0,':'),3,0,':')
AS DATETIME
)
-------------------------------------------------------------------------------------------------
--select * from ##temp1
--select * from ##temp2
select t1.Job_ID, t1.Name, t2.LastRunDateTime, t1.next_scheduled_run_date as NextRunDateTime, t1.Comment
from ##temp1 as t1
join ##temp2 as t2
on t1.Job_ID = t2.Job_ID
drop table ##temp1
drop table ##temp2
October 6, 2010 at 1:40 am
OK, that's a good start. When I run that on one of my servers, it returns the name of a job whose next run date is in the past - presumably because the job is disabled. Now, the question is this: is your problem that you think you've written the code correctly but it's returning the wrong code, or that there's a specific part of the code that you need help with writing? If you can point us to that, we should be able to help you.
John
October 6, 2010 at 11:23 am
My Final select Select Statement is a follows (Copy of earlier Code below.)
------------------------------------------------------------------------------------------------
SELECT t1.Job_ID,
t1.Name,
t2.LastRunDateTime, -- Retrieved from sysjobhistory
t1.next_scheduled_run_date as NextRunDateTime, -- Retrieved from sysjobactivity
t1.Comment
FROM ##temp1 AS t1
JOIN ##temp2 AS t2
ONt 1.Job_ID = t2.Job_ID
------------------------------------------------------------------------------------------------
Question: I would like to add another Column to the above Query
ExpectedLastRunTime [ **This will tell if the Last Job Run, ran on Schedule.
I would like to retrieve the ExpectedLastRunTime
only from SysSchedules ]
Thanks for your patience.
October 7, 2010 at 1:32 am
Does this need to work for absolutely any schedule, or are you going to use it only on certain types of schedule, for example transaction log backups that occur every 15 minutes? The reason I ask is that after you've taken into account the fact that jobs can be scheduled to run daily, weekly or monthly and maybe only between certain times of the day, your query will start to get very complicated. Bear in mind also that if the schedule is changed between when the job runs and when you run your query, this will not be taken into account.
Is it vital that you find out whether the job ran precisely on schedule, or is it sufficient to know that it ran in the last x minutes, where x is the number of minutes specified in the schedule?
John
October 7, 2010 at 10:41 am
Yes, I need to know if the Job Ran on Schedule.
My Query should also take into account Schedule Changes.
The only way this could be done is looking at the Columns in SysSchedules
[ enabled, freq_type, freq_interval, freq_subday_type, freq_subday_interval, freq_relative_interval
freq_recurrence, factor, active_start_date, active_end_date, active_start_time, active_end_time ]
and computing the LastExpectedRun and then verifying in JobHistory \ JobActivity if it did run.
I was wondering if there is a StoredProc that does the same OR
If anyone on the Forum had a similar issue in the Past and had the query handy.
Thanks
October 8, 2010 at 1:31 am
I'm afraid I don't know of any such stored procedure and I've never written a query myself to get this information. I don't think it's possible to take account of any schedule changes - you'd need there to be a table called sysjobschedulehistory, but that doesn't exist as far as I know! The only way I can think of to do this is putting a trigger on sysjobschedules to write to your own history table, but triggers on system tables are not recommended (and possibly not allowed).
A good place for you to start if you want to obtain code samples is to run a trace and then perform various tasks in the Job Activity Monitor.
Good luck
John
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply