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

 Last reviewed: April 8, 1997
Article ID: Q151157

The information in this article applies to:
  • Microsoft SQL Server, versions 6.0 & 6.5

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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 8, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.