October 20, 2016 at 3:40 am
I have some SQL Server jobs scheduled between 1:00 AM and 3:00 AM.
We have server downtime on the first Monday of every Month between 12:00 AM and 4:00 AM.
How can I ensure that all those SQL Jobs which did not run during this downtime will auto run upon server starts at 4:01 AM on those downtime Monday's.
Thanks
October 20, 2016 at 4:18 am
I think you have two options:
First create a stored procedure to check if the "last run" moment of the applicable jobs meets the requirements of the schedule. You can use this script as a starting point. If you are certain about the precise downtime period or only want it for just a specific period you can simplifiy the check, but that is up to you to decide. From the stored procedure you can start all jobs that have missed their schedule.
The two options I mentioned are about the way to execute the stored procedure:
1. make the stored procedure to auto start at SQL starts up with sp_procoption.
2. create a job to execute the procedure that runs after each downtime (when SQL Agent starts) or at a specific time if you are sure about the (or only care about specific) down time period.
October 20, 2016 at 4:27 am
Please don't post multiple threads for the same question. From the other thread:
GilaMonster (10/20/2016)
Create a second schedule for each of those jobs that runs on the 1st Monday of the month at something like 4:15 (give time for SQL and SQL Agent to start)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply