Solved

Maximum number of user connections

Posted on 1998-01-12
3
220 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
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 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

12 Experts available now in Live!

Get 1:1 Help Now