February 9, 2007 at 1:54 pm
Guys,
Does anyone have a script for identifying jobs which have a runtime being very close to the maximum alloted time for this job?
More specifically, if a job is scheduled to run every 2 minutes and its runtime is close to 2 minutes, it would be helpful to know about it, especially if the runtime has a potential to increase, so that we can prevent clashes/job failures.
Has anyone devised any solution for this problem or can advise any type of a solution?
I am aware of different systables in msdb database (sysjobs, sysjobschedules), but cannot figure out how to put things together to achieve my goal.
Thanks a lot for any pointers
February 9, 2007 at 4:35 pm
Modify the following script as needed...
select step_name,'run_date'= cast(h.run_date as varchar(8)),
'run_time'= replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),
'run_datetime' = left(cast(h.run_date as varchar(8)),4)+'/'
+substring(cast(h.run_date as varchar(8)),5,2)+'/'
+right(cast(h.run_date as varchar(8)),2)+' '
+left(replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),2)+':'
+substring(replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),3,2)+':'
+right(replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),2),
--run_duration = cast(h.run_duration as varchar(20)),
run_duration_InSeconds = case
when (len(cast(h.run_duration as varchar(20))) < 3)
then cast(h.run_duration as varchar(6))
WHEN (len(cast(h.run_duration as varchar(20))) = 3)
then LEFT(cast(h.run_duration as varchar(6)),1) * 60 --min
+ RIGHT(cast(h.run_duration as varchar(6)),2) --sec
WHEN (len(cast(h.run_duration as varchar(20))) = 4)
then LEFT(cast(h.run_duration as varchar(6)),2) * 60 --min
+ RIGHT(cast(h.run_duration as varchar(6)),2) --sec
WHEN (len(cast(h.run_duration as varchar(20))) >= 5)
then (Left(cast(h.run_duration as varchar(20)),len(h.run_duration)-4)) * 3600 --hour
+(substring(cast(h.run_duration as varchar(20)) , len(h.run_duration)-3, 2)) * 60 --min
+ Right(cast(h.run_duration as varchar(20)) , 2) --sec
end from msdb..sysjobhistory h where job_id =
(select job_id from msdb..sysjobs where name = 'Job name')
MohammedU
Microsoft SQL Server MVP
February 12, 2007 at 8:12 am
actually sql server deals with this situation quite well, I've encountered some log shipping scenarios where logs were being shipped every minute but the jobs actually took 4 mins to run. All that happens is sql starts the nect run after the first finishes, so in your example all that happens if your 2 min job over runs is that the next run starts after the one finishes.
I have code which does this but it uses a number of functions to convert the date/time formats in the tables so it's not very easy to post.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 12, 2007 at 2:27 pm
Thanks guys.
Colin, I am a bit confused from your explanation. Let's assume that I run the same job every 15 min from 2PM to 3PM (i.e. 5 times), but it takes 25 min to run. Are you saying that it will still run 5 times or only 2 times?
And if only 2 times, then does it mean if I have 2 completely jobs coinciding with time, the 2nd job will not get executed?
Thanks a lot
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply