February 25, 2014 at 8:48 am
Hello,
I have created a custom scheduler that has been running fine, but it depends on SQL Server Agent service. I want it to process missed schedules after a potential downtime.
CREATE TABLE [dbo].[Schedules](
[Id] [int] NOT NULL,
[Frequency] [int] NOT NULL,
[Time] [time](0) NOT NULL,
[Days] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO Schedules VALUES (1,1,'01:00:00',16);
INSERT INTO Schedules VALUES (2,1,'23:30:00',62);
INSERT INTO Schedules VALUES (3,1,'18:00:00',127);
INSERT INTO Schedules VALUES (4,2,'01:00:00',16);
INSERT INTO Schedules VALUES (5,2,'02:30:00',8);
INSERT INTO Schedules VALUES (6,2,'23:30:00',127);
GO
The following is the query I run every minute to capture the schedules to run at that minute.
DECLARE @dt datetime,
@tm time(0),
@wd int;
SELECT @dt = CONVERT(char(16),Getdate(),121); --Zero out seconds and milliseconds
--SELECT @dt = CONVERT(char(16),'2014-02-25 09:39:25.443',121);
SELECT @tm = CAST(@dt as time(0));
SELECT @wd = DATEPART(weekday,@dt) -1; -- Sunday=0, Monday=1,...,Saturday=6
SELECT Id, Frequency, Time, Days
FROM Schedules
WHERE
(frequency = 2 --Daily
AND days & power(2,@wd) <> 0
AND datepart(hour,time) = datepart(hour,@tm)
AND datepart(minute,time) = datepart(minute,@tm))
OR
(frequency = 1 --Hourly
AND datepart(minute,time) = datepart(minute,@tm));
Now, I need to create a query/sproc which will returns schedule ids between two dates. I will populate the dates; the datetime of last executed schedule (id) as @StartDate, and maybe GetDate() as @EndDate. I can later run that sproc as a strtup sproc for SQL Agent service.
usp_FindMissedSchedules @StartDate, @EndDate;
No simple approach comes to my mind.
Thanks,
Kuzey
February 25, 2014 at 11:20 am
I may be stupid and just missing something obvious, but I don't see where you're storing dates at all. You're only storing time and day of the week. So how could you then search for records within a given date range?
February 25, 2014 at 11:35 am
autoexcrement (2/25/2014)
I may be stupid and just missing something obvious, but I don't see where you're storing dates at all. You're only storing time and day of the week. So how could you then search for records within a given date range?
I am not creating or storing the dates, only the frequency. That's the challenge.
If it's an hourly job and SQL Agent was down for 3 hours, I should be able to SELECT that schedule which should have been run for 2-3 times.
February 25, 2014 at 1:42 pm
I want to make sure I understand before posting answers. You want the hourly schedule to show and run 3 times if the service was down for 3 hours?
February 25, 2014 at 2:03 pm
What if it's down for more than 24 hours? How can you tell with no dates?
February 25, 2014 at 5:12 pm
EricEyster (2/25/2014)
I want to make sure I understand before posting answers. You want the hourly schedule to show and run 3 times if the service was down for 3 hours?
Actually once is enough since they just repeat.
What if it's down for more than 24 hours? How can you tell with no dates?
If it's hourly; it should run every day and every hour.
If it's daily; it should run specified days at specified hour. If the autage is more than 24 hours, the script should check if the scheduled day and time is within outage window.
February 25, 2014 at 7:15 pm
ok, I think I understand the requirements. You need to store the last run time somewhere. Otherwise, if you start up at 14:15 on Tuesday, you have no idea if the hourly job ran at 14:00 or the Tuesday job ran this week. once you have that, the query becomes easy.
I have a thread scheduler that I use for .Net services which performs similar functionality only closer to the second and minute level. The best approach is to keep history of the job execution. You can get last run time by selecting the max(StartDateTime) from the log.
February 25, 2014 at 7:30 pm
EricEyster (2/25/2014)
ok, I think I understand the requirements. You need to store the last run time somewhere. Otherwise, if you start up at 14:15 on Tuesday, you have no idea if the hourly job ran at 14:00 or the Tuesday job ran this week. once you have that, the query becomes easy.I have a thread scheduler that I use for .Net services which performs similar functionality only closer to the second and minute level. The best approach is to keep history of the job execution. You can get last run time by selecting the max(StartDateTime) from the log.
I have the last run time in another table, and the @EndDate will basically be getdate().
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply