Get last job run duration in job history table

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 2005 Forums

Transact-SQL (2005)

Get last job run duration in job history table

Author

Topic

tonydang2002
Starting Member

15 Posts

Posted - 2008-12-05 : 19:19:01

Hi,
Can someone show me the sql script to get the last job run duration from the sysjobshistory table per job name from sysjobs.

Output I'm looking for:

Jobname LastJobRunDuration
Job1 00:05:11 (I want to see only the last run duration from the multiple historial runs)
Job2 00:00:57
...
...

Thanks,
Tony

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-05 : 19:40:23

This script could use some help, but you should be able to get the idea from it. I used another script of mine to develop it:


select 
	job_name,
	run_datetime,
	SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
	SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
	select 
		job_name, 
		DATEADD(hh, -7, run_datetime) as run_datetime, 
		run_duration = RIGHT('000000' + CONVERT(varchar(6), h.run_duration), 6)
	from
	(
		select 
			j.name as job_name, 
			run_datetime = max(CONVERT(DATETIME, RTRIM(run_date)) +  
				(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4)
		from msdb..sysjobhistory h
		inner join msdb..sysjobs j
		on h.job_id = j.job_id
		group by j.name
	) t
	inner join msdb..sysjobs j
		on	t.job_name = j.name
	inner join msdb..sysjobhistory h
		on	j.job_id = h.job_id and 
			t.run_datetime = CONVERT(DATETIME, RTRIM(h.run_date)) + (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4
) dt

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

raj1221
Starting Member

2 Posts

Posted - 2009-02-04 : 11:22:27

When i run the job, I receive the following default email message via SQL Server 2005 Database Mail Configuration Utility.

"JOB RUN: 'T_Weekly_Job' was run on 1/22/2009 at 4:02:54 PM
DURATION: 0 hours, 0 minutes, 1 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by User NETSINX\RAM-S. The last step to run was step 2 (Execute T SP). The job was requested to start at step 1 (RENAME WEEKLY)."

I also want to add the following message at the end of the default message generated from SQL Server 2005 Database Mail
"Please advise your technical support team as quickly as possible. "

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-02-04 : 12:21:21

moved from script library.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

raj1221
Starting Member

2 Posts

Posted - 2009-02-04 : 15:32:24

Can you please tell me what does "moved from script library" mean ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-02-04 : 15:38:32

it means i moved this post from script library forum since it doesn't belong there. it is not an answer to your question, just a notification why the edit happened.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

clpong
Starting Member

1 Post

Posted - 2009-08-14 : 01:18:24

quote:

Originally posted by tkizer

This script could use some help, but you should be able to get the idea from it. I used another script of mine to develop it:


select 
	job_name,
	run_datetime,
	SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
	SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
	select 
		job_name, 
		DATEADD(hh, -7, run_datetime) as run_datetime, 
		run_duration = RIGHT('000000' + CONVERT(varchar(6), h.run_duration), 6)
	from
	(
		select 
			j.name as job_name, 
			run_datetime = max(CONVERT(DATETIME, RTRIM(run_date)) +  
				(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4)
		from msdb..sysjobhistory h
		inner join msdb..sysjobs j
		on h.job_id = j.job_id
		group by j.name
	) t
	inner join msdb..sysjobs j
		on	t.job_name = j.name
	inner join msdb..sysjobhistory h
		on	j.job_id = h.job_id and 
			t.run_datetime = CONVERT(DATETIME, RTRIM(h.run_date)) + (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4
) dt

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Hi,
May I know why we need to minus 7 hours from the run_datetime?

select
job_name,
run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select
job_name,
DATEADD(hh, -7, run_datetime) as run_datetime,
run_duration = RIGHT('000000' + CONVERT(varchar(6), h.run_duration), 6)
from
(
select
j.name as job_name,
run_datetime = max(CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
group by j.name
) t
inner join msdb..sysjobs j
on t.job_name = j.name
inner join msdb..sysjobhistory h
on j.job_id = h.job_id and
t.run_datetime = CONVERT(DATETIME, RTRIM(h.run_date)) + (h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4
) dt

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-14 : 02:09:21

You don't need the -7 in my script. I had that in there as I was doing a timezone conversion (from GMT to PDT). It definitely needs to be removed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources