Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2006-04-02 : 22:42:37
|
| I need a table structure that can hold a job schedule, similar to scheduling jobs in SQL 2000. Jobs can occur: - once, - daily, every N days, frequency: one time, or periodic - weekly, every N Weeks on M,T,W,Th,F,S,S; frequency, one time, or periodic - monthly, etcNow, I could implement columns that reflect each of the conditions above, but I suspect there is a simple structure that addresses all the above... or not.Is there an optimal table structure that will simplify the tests to see if the job should be run at any given time?Sam |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-04-02 : 22:58:48
|
| Check out Books Online for a description of the sysjobschedules table in the msdb system database. It describes how they've done it based around bit-masking. I recently did a similar thing but simplified it to suit my application.HTH,Tim |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-04-03 : 08:06:52
|
| Thanks... - Why are the Dates and Times in sysjobschedules implemented as type INT? If this is a good idea for job scheduling, is it easy to cast DATETIME to Date INT and Time INT? - Having all the needed values in the Job Table, I can imagine two types of queries which a job scheduler could execute: The first uses complex logic, e.g., is this job enabled? Is it Daily, Weekly, Monthly? Periodic, or one time? What is the end-date? Do the conditions for this configured job meet the current date and time?The second approach which might not be possible, is that all the parameters for Daily, Weekly, Monthly, Periodic and One-time fit into a common set of Columns/Values. The Job Scheduler does a query that isn't case-specific. Something like: Job to run?I'm sure someone has worked this out somewhere.Sam |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-03 : 11:42:11
|
I work on the basis of setting the "next time to run" when a job completes.Then I check the Jobs table (say hourly) for anything "overdue". I run those, which in turn advanced the "next time to run".The "Next time to run" is calculated as the smallest multiple of the run-interval which is after the current time - so if a daily run is missed for, say, a week because the server was in the garage being fixed  then its next run time will be the daily anniversary time next after "now".The only downside with this is if the run-interval is changed. Then the existing next-run-time must be recalculated (or you live with this limitation).I have a number of pre-defined run-intervals, and variations on that are only allowed by adding new ones! So I have Hourly, Daily (at a "Quiet Time" defined for the database), Weekly ("Quiet time on the Quietest day"), Daily-weekdays-only (2am Tuesday-Saturday), Just-before-work-Start (6am Monday-Friday) - e.g. for Price Updates which might leave something on sale a $0.01 for the whole weekend if they ran on Saturday 6am and no-one noticed until Monday!Kristen |
|
|
|
|
|
|
|
|
Job Scheduling