Limit on Number of Concurrent Jobs ins SQL Agent
-
On one SQL Server 2000 machine, SQL Agent will only run 8 jobs simultaneously. If I try to start another, I get no error, but the jobs does not start. Once one of the other jobs has completed, I can start another, but never any more that 8.
Is there some limit in the agent configuration that I can modify?
-
I think you can change the concurrent job exec on sqlagent.
Pls refer
http://support.microsoft.com/kb/306457
But b4 proceeding pls take a backup of your registry.
Regards..Vidhya Sagar
SQL-Articles -
In SQL Server 2005, if the concurrent job exec reached maximum because so many jobs are running, then any jobs that are requested to start will be queued, and the agent will log a message that says:
"The job (%s) has been queued because the maximum number of working threads (%ld) are already running. This job will be executed as soon as one of the working thread finishes execution."
In SQL Server 2005, these values are stored in the syssubsystem table in the msdb database. SQL Server Agent queries these values by executing the sp_enum_sqlagent_subsystems stored procedure.
In SQL Server 2000, they are not queued and the agent logs this message:
"The job (%s) cannot be run because the maximum number of jobs (%ld) are already running. Please try this job again later."
This is for your information.
Regards..Vidhya Sagar
SQL-Articles -
The article specifies Worker Threads, but not, specifically, SQL Jobs. Using the article, I increased the threads, and still have the same problem. What is more confusing is that the log entry specifically states the maximum number of jobs as 8. Even after increasing the thread limit, this number did not change, and it is not any multiple of any thread limit value. Where is SQL Agent finding the 8?
-
Take a look at the Qn in this article about Concurrent Sql Server Agent Jobs
--------------------
Colt 45 - the original point and click interface
-
Thanks Phil. I found that, too, but it references the same MS article. I've quadrupled the parameters in the registry discussed by the article, and still am limited to 8 concurrent jobs. I'm still stymied by where the 8 comes from. Is it simply reporting to me the number of jobs it found running as the total allowed?
I have another server with the exact configuration (or so I thought), and it does not display this behaviour.
-
Strange.
Couple of things that should have been covered up front. What OS and SQL version are you running?
Are these jobs scheduled, or are you manually starting all of them?
--------------------
Colt 45 - the original point and click interface -
Windows Server 2003
SQL Server 2000 Enterprise with SP4
There is a master job which is scheduled, it has steps that start jobs using sp_start_job.
Trying to start the jobs manually gives the same result.
-
Ok, with that server setup you shouldn't run into any of the limitations imposed on the lower editions.
When you try to start them manually is this done via EM, or via sp_start_job?
Have you tried putting a time delay in the master job. There is a limit to the number of jobs you can start at once (it's referenced if one of the articles mentioned earlier).
--------------------
Colt 45 - the original point and click interface -
Phill, Thanks for your replies.
I've tried both EM and sp_start_job from QA when I have 8 jobs running. Neither method will start a ninth job.
In each step of the master job, I have a WaitFor Delay '00:00:05' statement prior to the start job statement.
I have written my own SP that will check to determine if the job actually started. If not, it throws an error, and the step waits 60 seconds and tries again.
-
Hmmm ... ok I'm out of ideas. Looks like you've tried all the avenues I can think off.
I'd say the next step would be a support call to MS
--------------------
Colt 45 - the original point and click interface
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply