April 12, 2004 at 2:31 am
Hi,
I am currently examining the SQL Server Job scheduling mechanisms, namely the MSDB database, in order to be inspired for my own scheduling system.
I have no difficulty in understanding how a job schedule is encoded in to sysjobschedules table and the meaning of each data field in this table. However I am really curious about how SQL Agent is able to know, if there are any jobs to be triggered, at a specific moment.
Posibilities were
-
SQL Agent runs a proper query on these sysjobs and sysjobschedules tables to get the jobs for that moment. (A rather weak possibility, since SQL Profiler lists no such activity from SQL Server Agent), or
-
SQL Server Agent reads the schedule data in to memory, and makes use of server resources to start each job at the precise time. (I mean setting of system interrupts, CPU related timing etc.)
Please let me know if you might have any sugesstions to this chaos I have ran in to.
Sincerely, Alex
Alexzander N. Nepomnjashiy
Technical editor for Wrox Press:
"SQL Server 2000 Fast Answers"
Technical editor for Apress:
"Honeypot for Windows"
"SQL Server Yukon Revealed"
April 15, 2004 at 8:00 am
This was removed by the editor as SPAM
April 15, 2004 at 8:52 am
While there may not be a "query" per se, the idea you have of reading the sysjobschedules in real time is probably what happens. Perhaps there is some "backdoor" approach used by the agent service to read the table. It must do something like this as the jobs are picked up as they are added. Have you run profiler as the agent started to see if it reads it then?
That could happen and then some type of trigger when you add a new job that also drops it into memory. A good test would be to "Add" your own row to sysjobschedules without using the GUI and see what happens.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 15, 2004 at 11:40 pm
Steve, thank you for your thoughts.
Alexzander N. Nepomnjashiy
Technical editor for Wrox Press:
"SQL Server 2000 Fast Answers"
Technical editor for Apress:
"Honeypot for Windows"
"SQL Server Yukon Revealed"
April 16, 2004 at 12:12 am
IMO jobs are "posted" into the active sqlagent-memory. This is done at startuptime of sqlagent or at job-modification time when you use the propre sp's.
Allerts with joblaunch are intercepted by sniffing the windows-eventlog (done by sqlagent).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply