470,591 Members | 2,165 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,591 developers. It's quick & easy.

Limiting active processes?

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?

Jul 23 '05 #1
2 1691

<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
Jul 23 '05 #2
Another idea is to check the system tables and see how many job are
currently "executing".
Check the books online because there is a status for "executing".
This is as long as the job is a predefined job. I created an form for the
computer operators in our shop so they can view the
status of job.

Oscar...

<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?

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by McKirahan | last post: by
1 post views Thread by Philippe Poulard | last post: by
2 posts views Thread by jason.m.ho | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.