Help: SQL Server stops responding to All Clients. WHY??? 
Author Message
 Help: SQL Server stops responding to All Clients. WHY???

We have developed a client-server program using MS Visual Basic 5.0
Enterprise Edition (SP3) running against MS SQL Server Ver 6.5 (SP4).
The VB application uses the UserConnection object to access the stored
procedures in the SQL Server.

The UserConnection properties are:
    ODBC Prompt Behavior = Never
    Cursor Library = Client-Batch cursors
    Login Timeout = 30 sec
    Query Timeout = 30 sec

The SQL Server runs on Windows NT 4.0 Server (SP3) with a single Pentium
Pro 200 CPU and 128 MB RAM, while the clients are Windows NT 4.0
workstations (SP3)
with Pentium 133 CPU and 32 MB RAM.

There are 2 types of applications we run; one for inventory control
purpose (with lots of data entries) while the other (OLTP) for data
collection from production machines and display/analysis purposes. Our
customers use the applications 24 hours a day every weekday. So far, we
have tested using 10 (out of 50) client connections (7 running latter
program simultaneously and 3 others running the inventory program) and
have already experienced some problems:-

On a regular basis (1 to 2 days after SQL Server and client programs
restarted),  the SQL server will stop responding to all clients. All
clients will continously get the message "Execution canceled" and will
not be able to perform insert/update/delete operations on the tables.
When we restart the client programs, we get the message "Communication
link failure."

When we try to run queries on the server side, all tables except three
of them, will return results. We believe these 3 tables are blocked but
don't know which trace flags to set since the problems happen after more
than 24 hours of use and "usually" during the night shift (after
midnight) when the SQL administrator is not around. To make matters
worse, the customers are overseas. So each time this happens, they have
to restart the SQL Server and the client programs.

We have tried to follow general guidelines suggested in books regarding
handling
deadlocks and performance issues to write our procedures, but still
cannot figure out
why the SQL Server stops responding to ALL CLIENTS when SOMETHING
HAPPENS (???). We thought the server will choose deadlock victims and
continue accepting new connection requests. We also wonder if this may
be due to a MS SQL bug and if a workaround is already available.

If anybody has any idea or solution for this problem, please let us
know.
Thanks a lot for your attention.

P/S: Would appreciate if you could also reply directly to:



Sat, 30 Sep 2000 03:00:00 GMT  
 Help: SQL Server stops responding to All Clients. WHY???

Edmund,

 First check whether SQL is accepting any new requests - i.e. do an
ISQL -Usa -P<password> -S<servername> from a client.  If your app can't
connect that may be to do with table blocking.

 Any errors appearing in the NT/SQK errorlog?

 Neil Pike MVP/MCSE
 Protech Computing Ltd



Sat, 30 Sep 2000 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Help: SQL Server stops responding to All Clients. WHY???

2. Help: SQL Server stops responding to All Clients. WHY???

3. Monitor on Server with MS SQL Server Does Not Respond

4. Client/Server DCOM not responding

5. client does not respond to an ActiveX.exe Server event

6. Help: VB Stops Responding

7. SQL server not responding..

8. Stopping SQL server service through VB program

9. SQL Server : Data has changed operation stopped.

10. Stop the SQL server process

11. Stop & Restart SQL Server Process

12. SQL Server : Data has changed operation stopped

 

 
Powered by phpBB® Forum Software