Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Where to find job history?

Author  Topic 

sql_er
Constraint Violating Yak Guru

267 Posts
Posted - 2006-12-14 : 16:37:54
Guys,

I have a job running every 15 mins. It is currently taking about 5 mins to execute. I am predicting that it will take more time, with time, but I need to make sure that it does not go over 15 min, as it will create problems as a new execution begins.

I know that information about the length it took to run a job could be found in job history. Would anyone know what system table can be queried to extract this information.

I am thinking to create another job to run periodically to check the length of those job executions to have heads up when the length of execution will approach 15 mins.


Thank you very much

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-12-14 : 16:39:21
msdb.dbo.sysjobhistory

Tara Kizer
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts
Posted - 2006-12-14 : 16:45:23
Ah ... great

Thanks!
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts
Posted - 2006-12-14 : 17:08:17
I am trying to sync the time between what is showing in job history and what is in msdb.dbo.sysjobhistory.

My latest job was run at 4:40 and it ran (according to history values I see in Enterprise Manager) for 2 min and 28 seconds.
sysjobhistory is showing very strange values, however. Namely:

run_time = 1000
run_duration = 224

How can I map between the two?

Thank you
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts
Posted - 2006-12-14 : 17:40:39
according to BOL:
quote:

run_time: Time the job or step started.
run_duration: Elapsed time in the execution of the job or step in HHMMSS format.


so in your case, the job started at 10am, and ran for 2m 24sec.

http://www.elsasoft.org
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-12-15 : 02:40:27
"as it will create problems as a new execution begins"

My understanding is that SQL Agent won't launch a new job if the current one is still running.

Kristen
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts
Posted - 2006-12-15 : 09:25:03
Jezemine: Thanks for clarification

Kristen: If the job won't launch, will it launch once the previous one finishes or only at the next scheduled run?

Thank you
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-12-16 : 04:46:43
My understanding is (sorry not enough time to check just now) that when a job finishes it schedules the next job for the next-anniversary-of-the-start-time.

So if the job was hourly and ran at 09:00 for 61 minutes, or 119 minutes, the next job would start at 11:00

This is something you need to watch out for with backup jobs. If a job backs up all the databases (alphabetically, say) then it may run a long time, and prevent getting some databases backed up within a reasonable time.

For example, a TLog backup that runs every 10 minutes might take an hour to run if there is an index rebuild just finished on one database. Or it may sit any wait whilst a Full Backup completes on a Big Database. That could easily cause NO Tlog backups to run for an hour or more

Kristen
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts
Posted - 2006-12-18 : 12:41:31
Thanks Kristen.

I guess I should try it out and see what happens.

Another question if someone can help me with: For all the jobs we have, I'd like to figure out the time difference, in minutes or seconds, between "how long the job ran vs time between successive scheduled runs".

To clarify, assume I have a job scheduled to run every day, every 15 mins. If this job runs for 3 mins, I want to have 12 mins as an output. The problem I have is extracting this "15 mins" information from the database (since I want to do it for all jobs at once). I know that we have items such as freq_type, freq_interval, freq_subday_type ..., which, once combined together in some way, can tell us this 15 min figure. But, I cannot figure out how to put all these together to know that it is 15 mins (or any other number, which could be 24 hours if the job runs once every day).

Thank you for any feedback
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-12-18 : 12:52:47
You'll have to display the Frequency columns in something more user-friendly, but you probably want something along these lines:

SELECT	H.job_id, H.step_id, 
	[Min]=MIN(H.run_duration), [Avg]=AVG(H.run_duration), [Max]=MAX(H.run_duration), 
	J.enabled, 
	S.freq_type, S.freq_interval, S.freq_subday_type, S.freq_subday_interval, S.freq_relative_interval, S.freq_recurrence_factor, 
	J.name
FROM	msdb.dbo.sysjobhistory AS H
	LEFT OUTER JOIN msdb.dbo.sysjobs AS J
		 ON J.job_id = H.job_id
	LEFT OUTER JOIN msdb.dbo.sysjobschedules AS S
		 ON S.job_id = H.job_id
		AND S.enabled = 1
WHERE	    H.run_date >= '20061201'
	AND H.run_date <  '20070101'
--	AND H.step_id = 0		-- Whole job only
GROUP BY H.job_id, H.step_id, J.enabled, J.name, 
	S.freq_type, S.freq_interval, S.freq_subday_type, S.freq_subday_interval, S.freq_relative_interval, S.freq_recurrence_factor
ORDER BY H.job_id, H.step_id

Kristen
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts
Posted - 2006-12-18 : 14:16:36
Thank you
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site