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,425 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

840 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