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
No comments:
Post a Comment