Waiting For Worker Threads -- SQL Server Agent ??? 
Author Message
 Waiting For Worker Threads -- SQL Server Agent ???

Hello all,

I am currently running a Windows 2k Advanced Server with SQL Server 2000
Enterprise SP1.  I have created a job that executes occasionally that will
dynamically create and start sub-jobs automatically.  At any given execution
of the procedure I have approximately 100 sub-jobs being created and fired
off.  For some reason, and I guess this leads into my question, the SQL
Server Agent only allows about 9 jobs to be running simultaneously.  The
other jobs not yet started have a status of "Waiting for Worker Thread."  I
have adjusted the Maximum Worker Threads under the SQL Server Properties -
Processor tab which in turn did not help.  While executing these jobs I have
monitored the CPU Usage and it hovers around 40%.  Is it possible to execute
more than 9 jobs simultaneously?  If so, please post help.

Thanks,
Scott G
DB Engineer



Wed, 28 Jul 2004 01:51:45 GMT
 Waiting For Worker Threads -- SQL Server Agent ???

Scott, SQL Server max worker threads setting is not applicable to SQL Server
Agent. Each job subsystem has a limit on number of concurrent jobs and is
set in the registry. Each subsystem has its own limit. It looks like you are
using a CMDExec or an ActiveScripting job. In SQL Server 7.0 this can be
configured in the registry at
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\SQLServerAgent\Subsystems

I don't have SQL Server 2000 handy to check this for you.
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


Hello all,

I am currently running a Windows 2k Advanced Server with SQL Server 2000
Enterprise SP1.  I have created a job that executes occasionally that will
dynamically create and start sub-jobs automatically.  At any given execution
of the procedure I have approximately 100 sub-jobs being created and fired
off.  For some reason, and I guess this leads into my question, the SQL
Server Agent only allows about 9 jobs to be running simultaneously.  The
other jobs not yet started have a status of "Waiting for Worker Thread."  I
have adjusted the Maximum Worker Threads under the SQL Server Properties -
Processor tab which in turn did not help.  While executing these jobs I have
monitored the CPU Usage and it hovers around 40%.  Is it possible to execute
more than 9 jobs simultaneously?  If so, please post help.

Thanks,
Scott G
DB Engineer



Wed, 28 Jul 2004 03:15:20 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Waiting for Worker Threads -- SQL Server Agent ???

2. SQL 2000 - Waiting on Worker Threads

3. SQL ---Waiting For Worker Threads----

4. SQL Server Agent Jobs - waiting for thread

5. Waiting for a Worker Thread

6. Waiting for Worker Thread

7. URGENT: Waiting for a worker thread

8. "Waiting for a worker Thread"

9. SQL Agent status "waiting for a worker thread"

10. Maximum number worker thread in SQL

11. max worker threads Option (SQL 7 sp2)

12. Process waiting after issuing OPEN CURSOR, onstat shows a thread in waiting condition await_MC1


 
Powered by phpBB® Forum Software