SQLAGENT job waiting on worker thread (306457)


The information in this article 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
This article was previously published under Q306457

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, then check the SQLAgent log for this 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 article contains information about how to modify the registry. Make sure to back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows registry



Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

The max_worker_thread setting is stored in the registry. If you have a default instance, this setting is stored under the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\Subsystems

 If you have a named instance, this setting is stored under the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\{YourInstanceName}\SQLServerAgent\Subsystems\LogReader

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 (7) 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:

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.
Modification Type:MajorLast Reviewed:5/3/2006
Keywords:kbprb KB306457
©2004 Microsoft Corporation. All rights reserved.