Solved

How to calculate an SQL Agent Job Completion Time from msdb..sysjobhistory table in SQL 2005/2008

Posted on 2011-09-22
7
1,313 Views
Last Modified: 2012-06-27
Hello,
Does anyone know how to calculate a SQL Agent Job completion time from msdb..sysjobhistory table in SQL 2005/2008?
Basically, I have to combine values from three columns : [run_date], [run_time], [run_duration].

Thank you in advance
0
Comment
Question by:JOSHUABT
  • 4
  • 2
7 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 36584656
they are int values, do you have any sample data for these 3 columns and a expected guess result :)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36584661
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 36584666
this is the answer I guess
CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)

+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime

Open in new window

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 5

Expert Comment

by:VENKAT KOKULLA
ID: 36585187

SELECT a.name AS job_name,
       a.enabled,
       a.description,
       a.date_created,
       a.date_modified,
       b.last_outcome_message,
       b.last_run_date,
       b.last_run_time,
       b.last_run_duration,
       convert(nvarchar(10),(b.last_run_duration/10000)) + ':' +
       convert(nvarchar(10),((b.last_run_duration%10000)/100)) + ':' +
       convert(nvarchar(10),(((b.last_run_duration%10000)%100)%100)) as duration,
       s.next_run_date,
       s.next_run_time,
       v.name,
       v.freq_type,
       v.freq_subday_type,
       v.freq_interval
  FROM MSDB.dbo.sysjobs AS a
  JOIN MSDB.dbo.sysjobservers AS b ON a.job_id = b.job_id
  JOIN MSDB.dbo.syscategories AS c ON a.category_id = c.category_id
  JOIN MSDB.dbo.sysjobschedules AS S ON A.job_id = s.job_id
  JOIN MSDB.dbo.sysschedules_localserver_view AS v ON s.schedule_id = v.schedule_id
 WHERE (c.name <> 'Report Server' )
 ORDER BY job_name


The above script will gives complete about the jobs running on the server with the job name, date modified,last outcoming message,last_run_date,last_run_time,duration,next_run_date,next_run_time etc;

Hope this will suffice your need.

--Venkat
0
 

Author Comment

by:JOSHUABT
ID: 36588515
Hainkurt,
Thank you for your response.
I think your T-SQL query answers my question.
I understand you convert run_duration to seconds and then divide it by total seconds of one day to covert it to day(s).
But one day has 86400 seconds and not 86399.9964.
Is there any reason why you divide it by 86399.9964 instead of 86400?

Thanks,
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36588685
to make exact precision you should use 86399.9964...
because one day is not actually 24*60*60 = 86400 exactly :) because of leap years etc...
if it does not matter +/- a few milliseconds, use 86400

0
 

Author Closing Comment

by:JOSHUABT
ID: 36588749
Thank you so much for your help.
I learned one day is actually shorter than I have thought to be for more than a half of centry :)


It is the question that enlightens us, not the answer.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now