INF: Max Number of Concurrent DB-Library Tasks in SQLExec

ID: Q151157

The information in this article applies to:

SUMMARY

In Microsoft SQL Server 6.5, the default number of maximum concurrent SQLExec tasks using a DB-Library connection has been increased to 61. This value is also configurable via a registry entry.


MORE INFORMATION

The limitation can be adjusted by adding the MaxDBProcesses Value Name to the following registry entry: 


   HKEY_LOCAL_MACHINE: SOFTWARE\Microsoft\MSSQLServer\SQLExecutive

The value should be added as a Data Type of REG_DWORD. Possible values are from 10 to 255 (Decimal). Please note that the actual number of concurrent tasks allowed will be two less (three less for SQL Server 6.5) than the number you specify in the registry value to allow for SQLExecutive overhead.

In Microsoft SQL Server version 6.0, this limitation defaults to 23 and is not configurable. Any task that is executed using DB-Library above this number will typically fail with a standard "Unable to connect to <server>" message written to the Task History.



A Transact-SQL task requires one DB-Library connection while LogReader and SYNC tasks each require two separate DB-Library connections. A Distribution task will only require one DB-Library connection when processing a "SYNC" job to a SQL Server subscriber. "SYNC" jobs occur because of an initial automatic synchronization or scheduled table refresh event. If a LogReader task is configured to run as "AutoStart" it will keep open its DB-Library connection(s) until the task is shutdown or fails. The Distribution task will only keep its DB-Library connection open when processing a "SYNC" job until the job completes even if it is scheduled as "AutoStart".

See Knowledge Base article Q89937, "INF: Getting Started with Microsoft SQL Server Replication," for more information about replication tasks.

If you encounter this problem on a distribution server using SQL Server 6.0 replication, use the following techniques:
  1. Schedule replication tasks so that the limit will not be reached keeping in mind the above description of how many DB-Library connections each task can consume.


  2. Spread the load of replication tasks across separate distribution servers. This can be accomplished by selectively grouping publishing servers with several different distribution servers instead of using one dedicated distribution server.


CmdExec tasks are not included in this calculation even if they execute a program that uses DB-Library (like ISQL.EXE) because a separate process is spawned to run this type of task.

Additional query words: dbsetmaxprocs 


Keywords          : kbusage SSrvAdmin SSrvRep 
Version           : 6.0 6.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 25, 1999