http://support.microsoft.com/kb/KB306457
If a scheduled SQLAgent job does not start or complete on time, check for the last run status. If the job status is "Waiting on Worker Thread," check the SQLAgent log for the following error message:
[251] Step %ld of job %s is being queued for the %s subsystem
By default, the SQLAgent log (Sqlagent.out file) is located in the Log folder of the SQL Server installation.
The number of concurrent jobs has reached the max_worker_thread setting for that subsystem of SQLAGENT.
Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
322756 (http://support.microsoft.com/kb/322756/ ) How to back up and restore the registry in Windows
The max_worker_thread setting is stored in the registry. If you have a 32-bit default instance, this setting is stored under the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\Subsystems
If you have a 64-bit default instance, this setting is stored under the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\SQLServerAgent\Subsystems
If you have a 32-bit named instance, this setting is stored under the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<YourInstanceName>\SQLServerAgent\Subsystems\LogReader
If you have a 64-bit named instance, this setting is stored under the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\<YourInstanceName>\SQLServerAgent\Subsystems\LogReader
Note Microsoft SQL Server 2005 stores the max_worker_thread setting in the syssubsystems table in the msdb system database instead of in the registry. The syssubsystems table can be updated to reflect a non-default max_worker_thread setting.
The very last part of each key value is a number that specifies the max_work_thread setting for that specific subsystem. In the following example, the LogReader subsystem has a maximum of 25:
LogReader:REG_SZ:C:\Mssql7\Binn\Sqlrepss.dll,C:\Mssql7\Binn\Logread.exe,ReplStart,ReplEvent,ReplStop,25
Increasing the number allows for more concurrent worker threads under that specific subsystem.
Note The Transact-SQL subsystem key is not created by default. To modify the Transact-SQL subsystem key, add a value of Transact-SQL with the datatype REG_SZ. In the string, add only a number such as 40 or 100. This number represents the new max_worker_thread setting for the Transact-SQL subsystem. For example:
TSQL:REG_SZ:100
There are seven subsystems in SQLAgent. The sp_enum_sqlagent_subsystems system stored procedure shows the current max_worker_thread setting for each subsystem. The following table shows the default settings:
After the total number of concurrent jobs that are running in the subsystem reaches the max_worker_thread setting, the additional job that starts in that subsystem is queued until one of the running jobs completes. This can be a problem for the LogReader subsystem. By default, the LogReader job is set to run continuously. If more that 25 LogReader jobs are started under the default LogReader subsystem setting, the queued jobs may never have the chance to run.
Because the Transact-SQL subsystem default is 20, the maximum number of concurrent Transact-SQL jobs is reached quickly if several Transact-SQL jobs are set to run. Add the Transact-SQL subsystem value as a string with a value that is higher than 20 if you notice that the Transact-SQL jobs are being queued for this reason.
A design limitation imposes a one second delay between jobs. This limitation was set so that the job scheduler does not monopolize the resources on the hosting server. Because of this limitation, up to 60 jobs can be started in the same one-minute interval. If jobs run and finish in less than one minute and are started at the same time based on a one-minute schedule, some jobs may never run. The jobs may never run because jobs that were already executed are retriggered every minute. Other jobs are left in a starvation situation.
Important This information applies to starting news jobs and does not impose a limitation on jobs that are running at the same time.
We recommend that you group jobs into batches of less than 60 simultaneous jobs and stage schedules so that there is enough bandwidth for SQL Server Agent to run these jobs