<mu************@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
We currently have a routine that "forks" out (to use the unix term)TSQL
commands to run asynchronously via SQL Agent jobs. Each TSQL command
gets its own Job, and the job starts immediately after creation.
Sometimes we can have too many of these jobs running at the same, and
the box crawls to a slow speed until the jobs finish up.
Is there a way we can limit the number of active jobs running under the
SQL Agent at one time? Or is there away to limit the number of active
(runnable) processes on SQL Server, in general?
I don't believe this is possible as such, although of course you could
create a table which each job UPDATEs to indicate how many active processes
there are. As for the number of active processes, again this isn't really
possible, without having a job running every few seconds to KILL the latest
processes to connect.
In general, limiting processes arbitrarily may not work very well; one day
you might find that your backup job didn't run because other jobs had taken
up all the 'slots', and then you've lost a day's data. It's may be better to
look at other solutions, such as using a multi-threaded script or program
which runs outside MSSQL.
Simon
