June 12, 2015 at 4:26 am
Hi All,
I wanted to check with you all if it is possible to create a script which generates the list of the schedules of all the jobs and then analyses it to rearrange the job starttime to ensure that we have equal gap between two jobs starting time. Just an idea to ensure we donot have many jobs running at the same time.
Cheers,
Devendra
June 12, 2015 at 8:33 am
Just threw this together quickly so take it as you will but it might help with what you are looking for.
USE [MSDB];
WITH Last_Run AS
(
SELECT MAX(instance_id) AS maxID, job_id FROM sysjobhistory WHERE step_id = 0 GROUP BY job_id
)
SELECT
s.name,
dbo.agent_datetime(sh.run_date,sh.run_time) AS runDateTime,
CAST(sh.run_duration/10000 AS VARCHAR) + ':'
+ RIGHT('00' + CAST(sh.run_duration/100%100 AS VARCHAR),2) + ':'
+ RIGHT('00' + CAST(sh.run_duration%100 AS VARCHAR),2) AS run_duration
FROM
sysjobs s
JOIN Last_Run l ON l.job_id = s.job_id
JOIN sysjobhistory sh ON sh.instance_id = l.maxID
WHERE
s.enabled = 1
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply