December 8, 2002 at 2:45 pm
We've found SQL Agent jobs as reasonably convenient for what one might think of as batch queues, a place you can submit jobs and let them run independently.
There are two restrictions, and fairly straightforward ways around them, but not very elegant.
One is that they can't parallel execute, e.g. job X can only have one instance running at a time. This is fairly moot until you consider the second.
The second is that it is difficult to have parameterized execution. There are times when one might want to do a SP_START_JOB and pass in a parameter that might effect how the job runs. For example, maybe the job is supposed to do a defragment on a table, it would be nice to set it running with the name of the table.
It's possible to work around the second by starting on a specific step, if the parameters you want are well defined ahead of time (say a dozen steps at the beginning that set the parameter to a dozen possible values).
It's possible to build an execution queue of parameters, and have the first step of the job look up the parameter of interest in this table, and clear it out when done.
And it's possible to build job definitions for one-shot use on the fly by scripting the job of interest, and created it with a unique name and executing it so that multiple instances can be running at once.
All these things are fairly inelegant however. But work.
My question is how people are approaching the need to "run batch jobs" against SQL? Any other approaches I've missed? Anyone written utility scripts that help this be more transparent, or any 3rd party tools for it (other than general job scheduling utilities which I'm familiar with -- hoping for something inside SQL).
December 8, 2002 at 4:50 pm
Putting the params in a table is a decent solution. At some point it gets unwieldy (possibly as soon as you start putting params in a table!). I won't say its the best solution, but once I get to the point of complexity I drop the solution in an application, then either run it from a job or run it from a machine other than the server depending on the load it produces. I generally see the restriction of the job running once as a positive, its a simple way to serialize processing so that the box doesnt get maxed out.
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply