3
Problem description
==============
- I have 4 clients and 1 server (SQL server)
- 3 clients are Monitoring console 1 client is operation console
- Monitoring console collects some data from the control unit and
store them into the Sql server
- The operation console then retrieve this data from the sql for
reporting and statistics purposes
- I am using ODBC connection
- The problem is that the operation console is not able to connect to
Sql server
- I receive Error message: “A connection could not be established to
fileserver-(SQL server). Unable to connect. The max number of 15 configured
user connections are already connected. System administrator can configure to
a higher value with SP_Configure”
- I receive the same error message in many conditions while I am using
the EM (Enterprise Manager) GUI
Environment
========
Windows NT 4.0
SQL 6.5
Troubleshooting (we have done & Analysis)
=====
- Seems that sql server has reached the max allowable number of
connections
- It might be some opened connection those never being closed by some
application.
- This problem also appears when 7.5 installed while 6.5 is also
installed, however I don’t have 7.5 installed
- We tried to perform:
Exec sp_configure ‘user connections’, 25
Go
RECONFIGURE
Go
And then try to restart the sql server
- I was unable to execute the above steps as I was receiving time out
- Unable to stop/restart Sql server, I had to reboot to start sql
server (I was being interrupted with the error message again)
- Check sql configuration parameters from EM, I checked:
1. “conn time out” : reasonable values for Min, Max and running
2. “worker threads”: reasonable values for Min, Max and running
3. “user connections”: reasonable values for Min, Max and running
- Seems no problem with configuration parameters (the major ones
related to the symptoms we have at least)
- Also we performed restar for sql server service through the
rebooting with no impact on the problem
- We tried to truncate the log file of the database as I suspected it
causes a problem, we performed the following procedure:
Backup LOG <your database name> with truncate_only
- Try to restart your sql server, no avail
- We checked scheduled tasks, as you mentioned that this problem
started to appear three weeks ago and I noted that when I restart the sql
server or stop the clients the problem disappears but reoccurs each Sunday,
we found one scheduled task which configured to run in the exact same time (on
Sunday) however you added that restarting your server is no more effective
to temporary solve the problem as before (and this is obvious through our
trials above to restart the server)
- We tried to remove that schedule task and then restart as it might
be the cause of the problem
- I was unable to remove the scheduled task, and I received another
error message related to Sql server!!
- While we were performing the above steps, I have been interrupted
with the same error message while I am using the query analyzer
- The server looks to be in very unstable state, so I need some
recommendations to fix the problem as mentioned bove.
Further work done:
I've tried to change the user connections to 0, which means unlimited connections are going to be accepted by SQL server, I found the error message was no more active for a long period before it
reactivated again at the end but with connections user 25! Through that time
I've received the following messages
"A connection could not be established to fileserver - (DB-Library) SQL
server connection Timed out".
"SQL server connection time out. Do you wish to retry?
"ERROR 10024 (DB Library) SQL server connection time out"
Msg No 10007 Severity 5 State 0. General SQL server error.
Here some more problem clarification that it could help fixing the problem
- The working data base is marked inaccessible and cannot be edited
- The following message appears when restart the server and before login
"The application log file is full".
