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_idWHERE b.run_status = '1' AND b.step_id = '0' AND a.enabled = '1' AND b.run_date >= 20100128Order 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:10Job1 1/28/2010 10:04:00 0:02:19Job1 1/28/2010 10:24:00 0:01:51Job1 1/28/2010 10:44:00 0:01:36Job1 1/28/2010 11:04:00 0:01:54Job1 1/28/2010 11:24:00 0:04:59Job1 1/28/2010 11:44:01 0:04:32Job1 1/28/2010 12:04:00 0:06:06Job1 1/28/2010 12:24:00 0:07:32Job2 1/28/2010 9:20:00 0:04:21Job2 1/28/2010 9:40:00 0:02:37Job2 1/28/2010 10:00:00 0:02:59Job2 1/28/2010 10:20:00 0:02:34Job2 1/28/2010 10:40:00 0:02:33Job2 1/28/2010 11:00:00 0:02:50Job2 1/28/2010 11:20:00 0:02:51Job2 1/28/2010 11:40:00 0:04:48Job2 1/28/2010 12:00:00 0:04:16Job2 1/28/2010 12:20:00 0:06:44Job3 1/28/2010 9:21:00 0:03:19Job3 1/28/2010 9:41:00 0:01:50Job3 1/28/2010 10:01:00 0:02:37Job3 1/28/2010 10:21:00 0:01:33Job3 1/28/2010 10:41:00 0:01:33Job3 1/28/2010 11:01:00 0:02:18Job3 1/28/2010 11:21:00 0:02:21Job3 1/28/2010 11:41:00 0:04:20Job3 1/28/2010 12:01:00 0:05:17Job3 1/28/2010 12:21:00 0:05:10Job4 1/28/2010 9:22:00 0:06:00Job4 1/28/2010 9:42:00 0:03:37Job4 1/28/2010 10:02:00 0:03:52Job4 1/28/2010 10:22:00 0:03:15Job4 1/28/2010 10:42:01 0:03:05Job4 1/28/2010 11:02:00 0:03:22Job4 1/28/2010 11:22:00 0:05:48Job4 1/28/2010 11:42:00 0:06:33Job4 1/28/2010 12:02:00 0:07:35Job4 1/28/2010 12:22:00 0:09:28
Any better idea?Canada DBA