Not enough 'user connections' available to start a new process
mully
Getting this message - There are not enough 'user connections'
available to start a new process.
I'm guessing I have a few options here either go into the dataserver
and increase the number of allowed users or go and kill off some users
if something is spawning too many or has got stuck.
My question is how do I actually get into the database, when I try and
login to do this the connection is refused.
Thanks in advance,
Mully
Rob Verschoor
configuration parameter 'number of user connections' with a command like
this:
sp_configure 'number of user connection', 50
(assuming you want to set it to 50; to display the current setting, just
leave off the comma and the 50)
You need to connect to the ASE server as a login with sa_role (i.e. DBA
priviliges). If you cannot acheive that, you have to figure that out first.
Perhaps there's a Sybase DBA in your organisation who can help you with
this?
HTH,
Rob V.
-------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase
Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (new edition!)
"The Complete Sybase ASE Quick Reference Guide" (new edition!)
"The Complete Sybase Replication Server Quick Reference Guide"
mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------
"mully" <niallp...@yahoo.co.uk> wrote in message
news:1181287917.6...@m36g2000hse.googlegroups.com...
mully
<r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
> You need to log in to the ASE server and increase the value of the
> configuration parameter 'number of user connections' with a command like
> this:
>
> sp_configure 'number of user connection', 50
>
> (assuming you want to set it to 50; to display the current setting, just
> leave off the comma and the 50)
>
> You need to connect to the ASE server as a login with sa_role (i.e. DBA
> priviliges). If you cannot acheive that, you have to figure that out first.
> Perhaps there's a Sybase DBA in your organisation who can help you with
> this?
>
> HTH,
>
> Rob V.
> -------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
> and Replication Server 12.5 / TeamSybase
>
> Author of Sybase books (order online atwww.sypron.nl/shop):
> "Tips, Tricks & Recipes for Sybase ASE" (new edition!)
> "The Complete Sybase ASE Quick Reference Guide" (new edition!)
> "The Complete Sybase Replication Server Quick Reference Guide"
>
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> -------------------------------------------------------------
>
>
> news:1181287917.6...@m36g2000hse.googlegroups.com...
>
>
>
> > Hi there,
>
> > Getting this message - There are not enough 'user connections'
> > available to start a new process.
>
> > I'm guessing I have a few options here either go into the dataserver
> > and increase the number of allowed users or go and kill off some users
> > if something is spawning too many or has got stuck.
>
> > My question is how do I actually get into the database, when I try and
> > login to do this the connection is refused.
>
> > Thanks in advance,
>
> - Show quoted text -
Thanks Rob,
I have dba privleges with my login but it still won't let me in, from
reading a little it seems that ASE reserves one connection for the SA
to perform these kinds of tasks in the event of this error,
unfortunately in this case it seems to be not working.
As the server is a dev box I guess my final option might just be to
kill the process in Unix and restart ?
Thanks,
Mully
Rob Verschoor
will not help for that (but give it a try).
If you need to get a new password set without logging in, boot the server
with the '-p sa' option which will generate a new password for the 'a'
login. There's a few more thing to say about this -- just google the
newsgroups.
HTH,
Rob V.
-------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase
Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (new edition!)
"The Complete Sybase ASE Quick Reference Guide" (new edition!)
"The Complete Sybase Replication Server Quick Reference Guide"
mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------
"mully" <niallp...@yahoo.co.uk> wrote in message
news:1181289519.0...@n4g2000hsb.googlegroups.com...
Luc Van der Veurst
If you know people who are logged in, let them stop their
application (or kill their application) and try to login again.
Rob, perhaps a user with sa-role can always login in current versions
(and if so, is that unlimited? if 10 people with sa-role try to login when
no more user connections are available, will they all succeed ?), but
that has not always been the case, so perhaps Mully is using an older
version.
Luc.
mully
wrote:
> "mully" <niallpmul...@yahoo.co.uk> wrote in message
Thanks for the input Luc, we are indeed using Ver 12.5.0.3 and once I
got a couple of users to kill their application connections I could
get connected.
Its strange I can see that waves of user connections to one particular
database are coming in and being released on quick succession, the
effect is that if a different user tries to connect to an other
database in the server at the crest of this wave they end up not have
a free connection to do so.
I increased the number of user connections which has certainly
alleviated the issue for now but I'm guessing there is something amiss
in one or more applications causing the wave \ pulsing effect.
If I run an sp_who I can see 100 or so connections with hostname = 0,
does this mean they are spawned on the server I've been trying to
determine what is causing this ? There are a small number of
additional processes listed with legitmate host \ login and origname
info.
Thanks in advance,
Mully
Mark K
isql -H0 and it will show a hostname of 0.
ASE reserves 1 connection for a user with sa_role. This was added in 12.5.1.
Mark Kusma
"mully" <niallp...@yahoo.co.uk> wrote in message
news:1181314671....@q75g2000hsh.googlegroups.com...
Mark A. Parsons
... snip ...
> Its strange I can see that waves of user connections to one particular
> database are coming in and being released on quick succession, the
> effect is that if a different user tries to connect to an other
> database in the server at the crest of this wave they end up not have
> a free connection to do so.
Getting a little bit away from the original intent of this thread ...
During the time that you're seeing this heavy volume of dis/connect activity you may want to run a brief sp_sysmon
session eg:
sp_sysmon "00:00:30", taskmgmt
In the output you're looking for the line labeled "Connections Opened".
In normal environments you'll usually see the 'per sec' number show up as less than 1 (eg, 0.7, 0.3, 0.1, 0.0).
If you find that this number is greater than 1, and you know this isn't a case of everyone coming into work at the same
exact time, then you may have a problem with a client application that's opening/closing connections as opposed to
keeping its initial connection open.
----------
I had one case several years ago where a developer had decided that it was wasteful to keep the database connection
open, so he decided to open/close a new connection each time the user did something (via the application).
There were about 1,000 concurrent users into the dataserver using this same application.
Net result:
- 20+ 'Connections Opened' per second
- dataserver bogged down due to the overhead of allocating resources for 20+ connections every second, plus the
deallocation of resources for 20+ other connections (we were maintaining a ~1000 spid count the entire time)
- Unix box bogged down due to the overhead of managing 20+ new network connections every second, plus the deallocation
of resources for 20+ other network connections
----------
In a more recent case the client was using a new 3rd party app (I want to say it was smartstream ... anyhoo ...) that
was constantly opening/closing connections, to the tune of 6-8 per second.
When I brought this up with the application group they seemed to know exactly what the problem was ... a configuration
setting in their connection pooling process.
They 'flipped' a switch and we suddenly dropped to something like 0.1 new connections per second ... and dataserver
performance went back to it's normal levels.
mully
Excellent thanks for those replies they have helped us identify a
rogue app frmo the client side !!