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
 SQL Server Administration (2005)
 Jobs overlap

Author  Topic 

CanadaDBA

583 Posts

Posted - 2010-01-28 : 08:09:59
SQL 2005 SP3

Is there a tool or an approach to find the jobs overlap? I need to prepare a list to have Job name and start time and an average of "how long it takes to complete".

Canada DBA

CanadaDBA

583 Posts

Posted - 2010-01-28 : 11:45:11
For the record, I developed the following query. It generates a list of the enabled jobs and their successful execution durations for a given period. Now I need to import them into a tool (MS Project) to see the overlaps.

SELECT a.name,
Substring(Cast(b.run_date as Char(8)), 1, 4)+'/'+Substring(Cast(b.run_date as Char(8)), 5, 2)+'/'+Substring(Cast(b.run_date as Char(8)), 7, 2) [Date],
Substring(Right('000000'+LTrim(Str(b.run_time)), 6), 1, 2)+':'+Substring(Right('000000'+LTrim(Str(b.run_time)), 6), 3, 2)+':'+Substring(Right('000000'+LTrim(Str(b.run_time)), 6), 5, 2) [Time],
Substring(Right('000000'+LTrim(Str(b.run_duration)), 6), 1, 2)+':'+Substring(Right('000000'+LTrim(Str(b.run_duration)), 6), 3, 2)+':'+Substring(Right('000000'+LTrim(Str(b.run_duration)), 6), 5, 2) [Duration]
FROM sysjobhistory b INNER JOIN sysjobs a
ON a.job_id = b.job_id
WHERE b.run_status = '1' AND b.step_id = '0' AND a.enabled = '1' AND b.run_date >= 20100128
Order by a.name, b.run_date, b.run_time

The sample results below might be slightly different than what the query generates because I imported the output into Excel and copied and pasted here. Excel might have affect the time or date format.

name Date Time Duration
------ ---------- -------- --------
Job1 1/28/2010 9:44:00 0:02:10
Job1 1/28/2010 10:04:00 0:02:19
Job1 1/28/2010 10:24:00 0:01:51
Job1 1/28/2010 10:44:00 0:01:36
Job1 1/28/2010 11:04:00 0:01:54
Job1 1/28/2010 11:24:00 0:04:59
Job1 1/28/2010 11:44:01 0:04:32
Job1 1/28/2010 12:04:00 0:06:06
Job1 1/28/2010 12:24:00 0:07:32
Job2 1/28/2010 9:20:00 0:04:21
Job2 1/28/2010 9:40:00 0:02:37
Job2 1/28/2010 10:00:00 0:02:59
Job2 1/28/2010 10:20:00 0:02:34
Job2 1/28/2010 10:40:00 0:02:33
Job2 1/28/2010 11:00:00 0:02:50
Job2 1/28/2010 11:20:00 0:02:51
Job2 1/28/2010 11:40:00 0:04:48
Job2 1/28/2010 12:00:00 0:04:16
Job2 1/28/2010 12:20:00 0:06:44
Job3 1/28/2010 9:21:00 0:03:19
Job3 1/28/2010 9:41:00 0:01:50
Job3 1/28/2010 10:01:00 0:02:37
Job3 1/28/2010 10:21:00 0:01:33
Job3 1/28/2010 10:41:00 0:01:33
Job3 1/28/2010 11:01:00 0:02:18
Job3 1/28/2010 11:21:00 0:02:21
Job3 1/28/2010 11:41:00 0:04:20
Job3 1/28/2010 12:01:00 0:05:17
Job3 1/28/2010 12:21:00 0:05:10
Job4 1/28/2010 9:22:00 0:06:00
Job4 1/28/2010 9:42:00 0:03:37
Job4 1/28/2010 10:02:00 0:03:52
Job4 1/28/2010 10:22:00 0:03:15
Job4 1/28/2010 10:42:01 0:03:05
Job4 1/28/2010 11:02:00 0:03:22
Job4 1/28/2010 11:22:00 0:05:48
Job4 1/28/2010 11:42:00 0:06:33
Job4 1/28/2010 12:02:00 0:07:35
Job4 1/28/2010 12:22:00 0:09:28

Any better idea?

Canada DBA
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 12:05:25
Useful script, Ta.

"Now I need to import them into a tool (MS Project) to see the overlaps."

SQL can do that!
Go to Top of Page
   

- Advertisement -