SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Jobs overlap
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 01/28/2010 :  08:09:59  Show Profile  Reply with Quote
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
Aged Yak Warrior

Canada
583 Posts

Posted - 01/28/2010 :  11:45:11  Show Profile  Reply with Quote
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

Edited by - CanadaDBA on 01/28/2010 11:55:24
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/28/2010 :  12:05:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000