The maximum connection limit has been reached

Hello PPL,

I do have an SQL2000 Database server that is giving me on some occations the error:
The maximum connection limit has been reached

I tried SP_WHO & SP_LOCK. But it seems not to be a locking problem. I'm very new to DATABASE administration so if you could point me in the right direction to look it would be preceated.

The thing is that SP_WHO shows by my knowledge extremly lot of sleeping process (In total a return of more then 26000 rows). Many are of the same defined user.

I know that the concurrent connections is set to default 32760 so getting pass this number gives me the error as displayed.

But how can I figure what or who is causing this problem. My thinking is that an application is causing the process going to sleep but never wakes them and just starts to use a new process instead of closing or reusing the old one.

So where should I look for?

Thanks in advanced,
Arjan Fraaij

Look in sysprocesses, you may find that connections aren't being closed properly.

Where is this error appearing? You can get similar errors due to NIC problems.

quote:Originally posted by mulhall

Look in sysprocesses, you may find that connections aren't being closed properly.

Where is this error appearing? You can get similar errors due to NIC problems.

Like I said I'm very new. Wich colum in sysprocesses whould indicate that the connections aren't closed as they should be?

Thanks Satya,

The first KB article I did know already thats where I discoverd the maximum connection of 32670.

This usefull for increasing the number of possible connection but not really what I'm looking for.

The total number of application users is arround 250ppl a number of more then 26000 concurrent sleeping processes is a bit to much I think.

I'm looking for how to determine the TSQL application that is causing all this sleeping process.

I've been looking for orphaned connections, but this is also not the case (Nothing to find with awaiting command in sysprocesses.

Maby this thread is in the wrong forum group?


Arjan

Run SQL profiler and log audit login and audit logout

use sp_who2 to quickly see connections

This will show conenctions and where they are coming from
Profiler will give more info such as application name (were available)

There is no way to see easily which connections should be closed (if sql knew this, it would probably be smart enough to close them for you).

But you can use this to see which application is generating the most connections and then use this to start debugging it

Hello and Thanks,

All right today I went to the site where the SQL server was hosted. Yesterday I tried by phone with the support user there to get some information. Because I needed some more I went my self today.

The support user didn't read the output correctly.

Today with only 5 users active on a specific application written in C++ I did see 16215 spleeping processes with Command status "Awaiting Command". So this are Orphaned Sessions after all. 5 minutes later Querying again gives 200 more processes.

Tuesday the programmer of the application is comming. I would like to know i Can figure out or this is a application programming error? So I can point him to the problem..

Or that this is a network environment error?

The application is hosted in a CITRIX application platform. Anyone who have seen this problem before?

How can I pin point the application problem, or figure out what is causing this? Users say they all needley shutdown there apps and citrix sessions...

Arjan