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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Get last job run duration in job history table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tonydang2002
Starting Member

15 Posts

Posted - 12/05/2008 :  19:19:01  Show Profile  Reply with Quote
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

Edited by - spirit1 on 02/04/2009 12:21:10

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 12/05/2008 :  19:40:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

raj1221
Starting Member

2 Posts

Posted - 02/04/2009 :  11:22:27  Show Profile  Reply with Quote
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. "
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

Posted - 02/04/2009 :  12:21:21  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

raj1221
Starting Member

2 Posts

Posted - 02/04/2009 :  15:32:24  Show Profile  Reply with Quote
Can you please tell me what does "moved from script library" mean ?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

Posted - 02/04/2009 :  15:38:32  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

clpong
Starting Member

Malaysia
1 Posts

Posted - 08/14/2009 :  01:18:24  Show Profile  Reply with Quote
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

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 08/14/2009 :  02:09:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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."
Go to Top of Page
  Previous Topic Topic Next 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.04 seconds. Powered By: Snitz Forums 2000