Maximum user connections

[victor regs]

We have a production server that is configured to 100 user connections.
Without any reason, the maximum connections had been exceeded and users
can no longer access the sql. But if you sp_who , it displayed only
4 user connections. Since, I have an open connection before this error
happens, i was able to execute sp_who. This happened 4 times since we
installed the sql server. the version is 6.50.422 sp5a with hotfix.

To all gurus out there, can you give light into this problem?

Thanks in advance.

Can you get into SQL Server now?


------------
victor regs at 7/29/99 9:12:08 AM

We have a production server that is configured to 100 user connections.
Without any reason, the maximum connections had been exceeded and users
can no longer access the sql. But if you sp_who , it displayed only
4 user connections. Since, I have an open connection before this error
happens, i was able to execute sp_who. This happened 4 times since we
installed the sql server. the version is 6.50.422 sp5a with hotfix.

To all gurus out there, can you give light into this problem?

Thanks in advance.

[victor regs]

Yes, after I rebooted the sql server , users can logged in.

------------
at 7/29/99 10:45:00 AM

Can you get into SQL Server now?


------------
victor regs at 7/29/99 9:12:08 AM

We have a production server that is configured to 100 user connections.
Without any reason, the maximum connections had been exceeded and users
can no longer access the sql. But if you sp_who , it displayed only
4 user connections. Since, I have an open connection before this error
happens, i was able to execute sp_who. This happened 4 times since we
installed the sql server. the version is 6.50.422 sp5a with hotfix.

To all gurus out there, can you give light into this problem?

Thanks in advance.

[B]

victor,
You should really watch this value with Perfmon. Raise the number of cofigured users. If you have maxed out 4 times already, it would be a safe bet that the 100 is not enough. Raise this to 200 and then watch perfmon. If you only hit 105 max users, then drop it back down to a more comfortable number. Always give yourself room. Once you lose all your connections, SQL will not allow additional connections until you restart the instance. Do not hold off on this. If the server locks, you have to reboot. You have been lucky so far to stop and restart SQL, but I have not always been so lucky. Several times I had to just shut down server. This can cause dbs to enter recovery or even go suspect if transactions do not get committed. This is just the way it is with SQL Server. Once you hit something like this, stopping and restarting will help get rid of the weird errors.
Hope this helps.