Solved

Maximum number of user connections

Posted on 1998-01-12
3
233 Views
Last Modified: 2008-03-06
Hi,

We´ve got a diinamic web application on IIS which accesses to a SQL Server in the same machine. The problem is that after browing it for a while an error happens:
the number of 30 user connections has been reached, use sp_configure to change the maximum number of user connections.

When I opn the Enterprise Manager, the maximum number of user connections is 32.767.

The asp´s of my application use ODBC. Is there a limit of ODBC user connections to a SQL Server database. Is it a problem of configuration?
0
Comment
Question by:evita
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 9

Accepted Solution

by:
cymbolic earned 100 total points
ID: 1089955
Each SQL server is licensed by number of connections.  Connections really don't care or relate to the access method used.  Under the covers, they are all ODBC to SQL server.  SO, your limit is your llicensed amount.  A common problem is software that does not close down connections, leaving unused but allocated connections that reduce the number of "real" connections available.  Another problem is applications that open multiple connections to access the server.  MS Access is one of those, using two connections each time.

Use your Enterprise manager to check the actual activity on the server when this occurs.  You may find a number of connections open, but little activity on the server, indicating idle connections are allocated.
0
 

Expert Comment

by:tehmul
ID: 1089956
Hi.  Your max connections are 32767 but that's not what you've configured the server for.  sp_configure gives the run value, the config value and the max value.  You need to look at the run value, not the max value.

Increase the no. of user connections by using

sp_configure "user connection", 90 --or whatever else you want
--Hope you have enough RAM
go

in isql/w
or use Enterprise Manager, click on the server, change its properties.
Then stop and restart the server.  That should increase the no. of connections.

Also, since you're using odbc to connect, check whether IIS maintains a persistent connection, I think it does.  But there's also an option to turn this off, somewhere I think.
Can get back later on this.
0
 
LVL 1

Expert Comment

by:alvinwang
ID: 1089957
why does access  open 2 connection at a time ?

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

733 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