Sybase ASE 12.5.4 EBF 14124 ESD#4 running out of connections

[Former Member]

I am working on a web application which works on SQL sever back end as well as Sybase ASE 12.5.4 EA server. Our project main focus was to move everything to sybase backend.

Earlier there were 2 databases, the app was connecting to - db1 on sql server, and db2 on sybase. Now we have created a separate namespace within db2 in sybase. So application only connects with db2, but with different users and in separate schemas. 

Application is in java and connecting to db server using jcon3 driver library.  It is using SybXADataSource driver class (provided by jcon3 jar) to connect to the server. Till last week application was able to connect to the server, everything was working fine (though I must say that code is still not optimized to use connection pools etc., I am opening  a connection, and closing it for every request).

For sometime, I have been facing an issue from the server- login failed, try again. In the server error logs I found it was throwing an error- "Error 1601 Severity 17 state 3: no user connections available to run the process" I checked for available connections (using sp_configure), allowed connections are 900, and total active connection at the time this error shows up is typically between 30- 100 (using sp_monitorconfig). Seems like this is well within limits.

Also yesterday, I did one more exercise, I restarted the server and logged in as sa from isql console only. Logged out and logged in again afimeter 20 minutes (no application connection was made), and isql login again gave this error. So I am inclined to think that something could be wrong with server configuration as well. Server is on linux red hat.

I have searched all the db forums and SCN website as well, and all of these refer to one resolution of increasing the number of connections, but as from my observation regarding that, I do not think it is an issue.

Any help would be greatly appreciated!!

[Former Member]

Hi Mark

This was helpful indeed! sp_monitorconfig output is not very predicatable, but sp_who was very informative. I realized that even on a server reboot and only sa login, it returns 900+ rows for the connections, all belonging to previous application user login, mostly in

state- "recv_sleep" and

cmd- "awaiting command".

Seems like the server is keeping a record of all the previous transactions/connections...

Could it be the driver class issue which I am using ( I am using SybXADataSource provided by jcon3 library)? In my code it does show that for every connection I open, I send a close() command.

Thanks in anticipation...

[Mark_A_Parsons]

NOTE: I'm a server side guy (ie, ASE DBA) and try to stay away from the nitty gritty application details if I can help it. 😉

A server reboot will clear out all application logins.

If you're seeing 900+ application logins shortly after reboot then it sounds like an application (perhaps middleware connection pools?) are auto-reconnecting when the dataserver comes back up.

Take a look at the contents of master..sysprocesses and master..monProcessLookup.  These tables contain details on connections (eg, host/ipaddr and OS spid of the client application, perhaps name of client application).  You should be able to use this info to track back to the application(s) that are managing all of those 900+ connections.

[Former Member]

This sounds like a valid explanation. Thanks so much. Do you think using connection pool at application level should resolve this issue? I also read about setting login triggers for users at the server side as a solution. Is it a good solution?

Regards

Monica

[Mark_A_Parsons]

Connection pooling is a good idea if a) it makes sense and b) it's done correctly.  If you do have some middleware reconnecting those 900+ connections then you'll need to talk with the folks who manage the middleware to see why 900+ connections are being maintained; 900 may be too high, or too low, it all depends on your environment.

As for login triggers ... what would be the purpose of the login trigger in your case?  By itself a login trigger doesn't provide any benefit ... it's the logic inside the login trigger that may provide a benefit ... so what logic would you place in the login trigger that would help with your current situation?

[Former Member]

One more thing I wish to clarify, why does the connection not closed when I am calling Close() on the connection from application side? I am sorry if this sounds too foolish...

[Mark_A_Parsons]

Sorry, that's outside my field of knowledge (ie, falls under "nitty gritty application details").