SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Where to find job history?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 12/14/2006 :  16:37:54  Show Profile  Reply with Quote
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

USA
36619 Posts

Posted - 12/14/2006 :  16:39:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
msdb.dbo.sysjobhistory

Tara Kizer
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 12/14/2006 :  16:45:23  Show Profile  Reply with Quote
Ah ... great

Thanks!
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 12/14/2006 :  17:08:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 12/14/2006 :  17:40:39  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 12/15/2006 :  02:40:27  Show Profile  Reply with Quote
"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 - 12/15/2006 :  09:25:03  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 12/16/2006 :  04:46:43  Show Profile  Reply with Quote
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 - 12/18/2006 :  12:41:31  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 12/18/2006 :  12:52:47  Show Profile  Reply with Quote
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 - 12/18/2006 :  14:16:36  Show Profile  Reply with Quote
Thank you
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000