Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-22
7
Medium Priority
?
1,649 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 59

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 59

Expert Comment

by:HainKurt
ID: 36584661
0
 
LVL 59

Accepted Solution

by:
HainKurt earned 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 59

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

704 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