January 8, 2020 at 2:55 pm
Hi All,
Happy new year 2020. It been long time I am here.
There is a job running for me daily @ 4 AM and @ 4 PM daily
If any of the job failed say if @ 4 AM job failed it will be retriggered @8AM/9AM based on some business logic and if 4PM job failed it will be retriggered @9 PM based on some business logic.
My jobs will be running for almost 3-5 hrs per run , twice a day if there are no issues, but if there are any issues then only it will be retriggered.
Please not that I don't have access to SQL Server agent , we can refer logs from our internal table.
The internal table will starttime and endtime of each runs
my question is how can we take jobs @ 4AM and jobs run @4PM separately and any interim runs happened for that day separately .
starttime is only known for 4AM and 4PM jobs , job endtime is unknown as it may run for more than 3-6 hours sometime.
For Eg:
startime endtime,runid
‘1/1/2020 4:00:00’, ‘1/1/2020 5:00:00’,1
‘1/1/2020 5:00:00’, ‘1/1/2020 5:30:00’,2
‘1/1/2020 5:30:00’, ‘1/1/2020 6:00:00’,3
‘1/1/2020 6:00:00’, ‘1/1/2020 7:00:00’,4
‘1/1/2020 7:00:00’, ‘1/1/2020 7:15:32’,5
‘1/1/2020 16:00:00’, ‘1/1/2020 16:57:00’,1
‘1/1/2020 16:57:00’, ‘1/1/2020 17:34:00’,2
‘1/1/2020 17:34:00’, ‘1/1/2020 18:02:00’,3
‘1/1/2020 18:03:00’, ‘1/1/2020 19:00:00’,4
‘1/1/2020 19:00:00’, ‘1/1/2020 20:57:44’,5
‘2/1/2020 4:00:00’, ‘2/1/2020 5:10:00’,1
‘2/1/2020 5:12:00’, ‘2/1/2020 5:30:00’,2
‘2/1/2020 5:34:00’, ‘2/1/2020 6:12:00’,3
‘2/1/2020 6:15:00’, ‘2/1/2020 7:20:00’,4
‘2/1/2020 7:24:00’, ‘2/1/2020 7:55:32’,5
‘2/1/2020 9:23:00’, ‘2/1/2020 9:56:00’,1
‘2/1/2020 10:02:00’, ‘2/1/2020 10:30:00’,2
‘2/1/2020 10:34:00’, ‘2/1/2020 11:19:00’,3
‘2/1/2020 11:19:00’, ‘2/1/2020 12:20:00’,4
‘2/1/2020 12:24:00’, ‘2/1/2020 13:05:32’,5
‘2/1/2020 16:00:00’, ‘2/1/2020 16:47:00’,1
‘2/1/2020 16:50:00’, ‘2/1/2020 17:32:00’,2
‘2/1/2020 17:34:00’, ‘2/1/2020 18:08:00’,3
‘2/1/2020 18:10:00’, ‘2/1/2020 19:22:00’,4
‘2/1/2020 19:22:00’, ‘2/1/2020 20:57:44’,5
Thanks
January 8, 2020 at 6:41 pm
Was just looking at the sample data you have in the attachment and none of those start at 4:00 AM or 4:00 PM. It looks like they start around 2:01 and some random interval of seconds.
Also looking at the data, it looks like jobs can start while others are running? This is what shows up in rows 1 and 2. Row 1 ends after row 2 has started.
If you are wanting all of the jobs that ran before 4:00 PM, you could use the datepart function (such as WHERE DATEPART(hour, StartDate) < 16). This could be used to tell you everything that ran before or after a specific hour and it would show you every day.
Sample code:
-- Get all jobs run before 4:00 PM
SELECT startTime, Endtime, runID
FROM <schema>.<table>
WHERE DATEPART(HOUR,startTime) < 16
-- Get all jobs run on or after 4:00 PM
SELECT startTime, Endtime, runID
FROM <schema>.<table>
WHERE DATEPART(HOUR,startTime) <= 16
Is this what you were looking for? Depending on the size of the table, this may not be efficient enough.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply