Maximum Number of Processes Reached

I am trying to setup transactional replication between Server A and Server B. There are 265 databases on each server.

I am running SQL Server 2005 on Windows Server 2003. The problem comes in at the 201st database. The message in the SQL Server Agent Error Log is :

Warning,[398] The job (WSSWPG09-EmpirePaint-WSSWPG06-104) has been queued because the maximum number of working threads (400) are already running. This job will be executed as soon as one of the working thread finishes execution.

SQL Server's max worker threads is set to 0. The Distribution, LogReader and T-SQL subsystems have been increased to 200 max_worker_threads.

Is there some other setting (maybe a Windows Registry setting) that can be configured to fix this? Or have I just hit a physical maximum of the processor?

Any help is much appreciated.
Maybe engine forum can answer question about max_worker_threads.|||

That is a lot of databases to syncronize between two servers...

Max worker thread set to 0 means use all available threads.

There are 255 threads per logical CPU.

IF you are running out of threads, you may wish to experiment with using 'fibers' (timeslicing the threads).

Go to the Server Properties Window, and on the CPU tab, check 'Use Windows Fibers'.

I recommend testing any changes.

|||

Check out http://www.microsoft.com/technet/technetmag/issues/2006/10/SQLQA/default.aspx

This talks a bit about your issue.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Maximum Number of Processes Reached

I am trying to setup transactional replication between Server A and Server B. There are 265 databases on each server.

I am running SQL Server 2005 on Windows Server 2003. The problem comes in at the 201st database. The message in the SQL Server Agent Error Log is :

Warning,[398] The job (WSSWPG09-EmpirePaint-WSSWPG06-104) has been queued because the maximum number of working threads (400) are already running. This job will be executed as soon as one of the working thread finishes execution.

SQL Server's max worker threads is set to 0. The Distribution, LogReader and T-SQL subsystems have been increased to 200 max_worker_threads.

Is there some other setting (maybe a Windows Registry setting) that can be configured to fix this? Or have I just hit a physical maximum of the processor?

Any help is much appreciated.
Maybe engine forum can answer question about max_worker_threads.|||

That is a lot of databases to syncronize between two servers...

Max worker thread set to 0 means use all available threads.

There are 255 threads per logical CPU.

IF you are running out of threads, you may wish to experiment with using 'fibers' (timeslicing the threads).

Go to the Server Properties Window, and on the CPU tab, check 'Use Windows Fibers'.

I recommend testing any changes.

|||

Check out http://www.microsoft.com/technet/technetmag/issues/2006/10/SQLQA/default.aspx

This talks a bit about your issue.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

maximum number of processes already allocated

Hi all,
Sql server 7.0
our client is getting the below error while accessing the
web application
Maximum number of DBPROCESSes already allocated
when we check the server from our side its working fine.
please let us know is the solution for this.
TIAHi,
Can you please check the "No of user connections" configured in Client's SQL
server.
How to check
From query Analyzer execute the below statement
sp_configure 'user connections'
If the run value is '0' then the allocation is dynamic and problem should be
because of some other issue. Incase if the run value is very low then
increase the value to a higher amount using. Eg to increase it to 100
connections.
sp_configure 'user connections',100
go
reconfigure with override
After this restart the MSSQL server service. If they still have issues
please attach the SQL server error log created during problem situation.
Thanks
Hari
MCDBA
"haseeb" <anonymous@.discussions.microsoft.com> wrote in message
news:a62d01c406e5$0c0e07a0$a301280a@.phx.gbl...
> Hi all,
> Sql server 7.0
> our client is getting the below error while accessing the
> web application
> Maximum number of DBPROCESSes already allocated
> when we check the server from our side its working fine.
> please let us know is the solution for this.
> TIA
>