Maximum number of user connections

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?
evitaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cymbolicCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tehmulCommented:
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
alvinwangCommented:
why does access  open 2 connection at a time ?

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.