SQL Server 2000 Database Connections

Posted on 2004-11-08
Last Modified: 2012-05-05
Below is my connection string that is in my application. The connection works fine, but I just had 12 users open the application to test it. The first 9 or 10 were fine, but a couple kept getting a time out error and never get into the application. Does SQL Server have a default that limits the connections to a database?

Private Sub Class_Initialize()

Set mDataAccess = New cDataAccess
mDataAccess.cnxString = "Driver={SQL Server}; Server=dohsdbschd0601; Database=AIDS; Uid=sa; Pwd=caffeine"

End Sub
Question by:jmoneilthe2
    LVL 8

    Expert Comment

    No the default is 32,767.

    The default number of maximum connections can be changed.

    From Books Online:

    How to set the SQL Server connection (Enterprise Manager)
    To set the SQL Server connection

    Expand a server group, and then expand a server.

    Expand Management, right-click SQL Server Agent, and then click Properties.

    Or use TSQL

    Use the "user connections" option to specify the maximum number of simultaneous user connections allowed on Microsoft® SQL Server™. The actual number of user connections allowed also depends on the version of SQL Server you are using and the limits of your application(s) and hardware. SQL Server allows a maximum of 32,767 user connections.


    user connections helps avoid overloading the server with too many concurrent connections. You can estimate the number of connections based on system and user requirements. For example, on a system with many users, each user would not usually require a unique connection. Connections can be shared among users. Users who are running OLE DB applications need a connection for each open connection object, users who are running ODBC applications need a connection for each active connection handle in the application, and users who are running DB-Library applications need one connection for each process started that calls the DB-Library dbopen function.

    Important  If you must use this option, do not set the value too high because each connection takes approximately 40 kilobytes (KB) of overhead regardless of whether the connection is being used. If you exceed the maximum number of user connections, you receive an error message and are not able to connect until another connection becomes available.

    user connections is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change user connections only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.

    LVL 8

    Expert Comment


    If you were exceeding the number of user connections you would recive an error that would indicate the fact.  However your problem could be a network issue at your router, switch or server nics, a server hardware problem an issue.

    The worstations that are not able to connect, I assume have connected at some stage?

    You may perhaps try a different driver. Have you tried the oledb drvier instead of odbc.

    mDataAccess.cnxString ="Provider=sqloledb;Data Source=dohsdbschd0601;Initial Catalog=AIDS;User Id=sa;Password=caffeine;"
    LVL 9

    Expert Comment

    Is this in ASP or VB? There are few things you can do to fix this depending on where you are using the connection.
    If it is ASP you can change a time out in your IIS and some code in ASP.

    The default for connection strings to DB is 20 secounds. If within 20 secounds you dont get results from DB the connection will be terminted and you will get a time out error. Naturally more people connected to DB it will become a little slower depending on hoe much RAM and CPU speed you have. Basically if you are expecting more than 10 users on your system and it fails you need more RAM and CPU, however you can set the time out to unlimited. But then this way the users wont be to happy.
    LVL 9

    Accepted Solution

    There are several places that could affect the time out:

    SQL Server

    Here is how in SQL Server:
    In Enterprise Manager, select Microsoft SQLServers. Then go to Tools/Options, go to the Connection tab and change Query time-out(seconds) to whatever you want.

    This is how in your code different ways:
    dbConnString = "DSN=someconnection;uid=myuserid;pwd=mypassword;"
    set dbConn = server.createobject("adodb.connection")
    dbConn.CommandTimeout = 300 (dbConnString)

    <% Server.ScriptTimeout = 150 %>
    0 is (wait indefinitely)

    Connection.ConnectionTimeout = 60

    Other soloutions:
    Index you tables
    Return less data from DB
    Optimize your Query


    Author Comment

    Thank you for the information. I think I resolved my issue by setting a table to have a primary key. When I stepped through the code, I noticed that it was crapping out when it ran a query to my lookupUser table. In that table, I simply have an ID, and a username but I never set the ID to be a primary key. Do you think that by not having an index to the table that when to many connections were made it locked up? It looks like that was the problem but I'm not sure.


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now