Solved

Maximum number of user connections

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how the fundamental information of how to create a table.

778 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