SQL Server randomly drops client connections

Question

Post reply

SQL Server randomly drops client connections

xnl28

Ten Centuries

Points: 1094
More actions
June 9, 2004 at 8:33 am

#85823

Help!!
Problems with our SQL Server - connections are dropping randomly. I can't see any pattern to this yet.
Here is what happens: I can happily run a queries in Query Analyser on my machine (connected to our SQL Server). After a period of time (that ranges from 1 minutes to 2 hours) the query reports an error, and this message is displayed:
     [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
     Server: Msg 11, Level 16, State 1, Line 0
     General network error. Check your network documentation.
     Connection Broken
The network connection between my machine and the server is fine - I have 9 other Query Analyser connections running the same query in the same database, but they are OK. If the physical netowork link dropped temporarily, then ALL those connections would report the same error.
Is there any tool I can use to find out why this is occurring? I have run Profiler, and this shows the connection logout, but that doesn't really tell me anything. What can I do to find out more about this error? For example, is this error generated by the client or the server? Is it initiated by the client or the server? etc...
Any help appreciated,
Steve

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Alamir SSCertifiable Points: 6085 More actions · Answer 1

Check that your Client Network utility is the same for any 2 clients had QA on them..

aslo open EM and from Tools menu, choose options .. in advanced tab , check that query time out is 0 (unlimited)

I hope this help u

Alamir Mohamed
Alamir_mohamed@yahoo.com

xnl28 Ten Centuries Points: 1094 More actions · Answer 2

Thanks for your reply.

All 10 queries were running from the same client - 10 query windows.

The "Query time-out" setting is 600 seconds. But isn't the Timeout property only valid for inactive conenctions? None of my 10 queries should ever timeout because they are running in a loop constantly, and are never inactive.

Also, the connection is dropped sometimes after 3 minutes, sometimes after 2 hours - never is it a regular 600 seconds!

Besides, the error I get is a general network error; if it was indeed a timeout, wouldn't SQL return a message mentioning that a timeout had occurred?

Alamir SSCertifiable Points: 6085 More actions · Answer 3

take care .. this network general error may happened to to Timeout expired ...

you may face some problems with Locking .. Please read this carefully:

When you want a report which based on complicated queries joined from several tables, These tables are updated frequently, then you lock these tables (or you will wait for previous transaction lock on these tables to be completed to put your locks on them) .. so all other poeple using these tables are locked too (so hang happened - because they are in the queue of lock of these tables)!!

the best thing is to run the query on the server (by making a view on SQL SERVER) .. and with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other transaction will complete successfully (for you and others) .

you will write something like this:

select Invoices.*, Situation.*

from Situation with (nolock) INNER JOIN Invoices with (nolock)

ON Situation.SituationID = Invoices.Situation

where Situation.SituationID =1

Disadv:

- when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.

Adv:

- no hang at all

- very fast Response

- Little summary for Locks in SQL Log file.

also : you may need to add more Memory (RAM) when server still hangs a little after transfering your queries to Server.... becuase every transaction or query is done on server first, then processed data is passed to your PC.

I hope this will help you to solve the problem

Alamir Mohamed
Alamir_mohamed@yahoo.com

xnl28 Ten Centuries Points: 1094 More actions · Answer 4

Thanks for your reply, Alamir.

However, I don't believe the problem is related to either locks (the problem exists even when no tables are being referenced, therefore no possibility of a locking problem), or memory (server has 4GB of RAM)

Here is the query that I run:

while 1=1

begin

select GetDate()

waitfor delay '00:00:03'

end

This should run continuously without ever stopping, and without the connection disappearing. Yet after 3 minutes or so, I get the error message that I described in my first post.

Any other ideas?

Alamir SSCertifiable Points: 6085 More actions · Answer 5

I think you have to use "Performance" tool in Windows and add counters of network ..

also check that yout TCP/IP and named piped are enabled in both Client Network utility and Server network utility (the both tools can be found from ... Start >> Programs>> Microsoft SQL Server ...

I hope this help u

Alamir Mohamed
Alamir_mohamed@yahoo.com

David Burrows SSC Guru Points: 65142 More actions · Answer 6

I used to get this with SQL7.

I installed SQL7 SP4 and MDAC 2.7 (on both client and server) and problem went away.

Far away is close at hand in the images of elsewhere.
Anon.

Alamir SSCertifiable Points: 6085 More actions · Answer 7

of course install SP3a for SQL Server 2000 or SP4 for SQL Server 7 ..

SP solved many problems.

Alamir Mohamed
Alamir_mohamed@yahoo.com

xnl28 Ten Centuries Points: 1094 More actions · Answer 8

Thanks for the advice.

Sp3a has been applied, and MDAC 2.8, but the problem remains...