SQLAGENT job waiting on worker thread

Article translations Article translations
Article ID: 306457 - View products that this article applies to.
This article was previously published under Q306457
Expand all | Collapse all

SYMPTOMS

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.

CAUSE

The number of concurrent jobs has reached the max_worker_thread setting for that subsystem of SQLAGENT.

RESOLUTION

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 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

STATUS

This behavior is by design.

MORE INFORMATION

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:
Collapse this tableExpand this table
SubsystemDescriptionSubsystem_dllAgent_exeStart_entry_pointEvent_entry_pointStop_entry_pointMax_worker_threads
ActiveScriptingActive Scripting SubsystemD:\Mssql7\Binn\Sqlatxss.dllNULLActiveScriptStartActiveScriptEventActiveScriptStop10
CmdExecCommand-Line SubsystemD:\Mssql7\Binn\Sqlcmdss.dllNULLCmdExecStartCmdEventCmdExecStop10
DistributionReplication Distribution SubsystemD:\Mssql7\Binn\Sqlrepss.dllD:\Mssql7\Binn\Distrib.exeReplStartReplEventReplStop100
LogReaderReplication Transaction-Log Reader SubsystemD:\Mssql7\Binn\Sqlrepss.dllD:\Mssql7\Binn\Logread.exeReplStartReplEventReplStop25
MergeReplication Merge SubsystemD:\Mssql7\Binn\Sqlrepss.dllD:\Mssql7\Binn\Replmerg.exeReplStartReplEventReplStop100
SnapshotReplication Snapshot SubsystemD:\Mssql7\Binn\Sqlrepss.dllD:\Mssql7\Binn\Snapshot.exeReplStartReplEventReplStop100
TSQLTransact-SQL Subsystem[Internal][Internal][Internal][Internal][Internal]20
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.

Properties

Article ID: 306457 - Last Review: May 13, 2008 - Revision: 7.1
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbprb KB306457

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com