SQL Server loosing connections
I have posted this in the SQL Server Data Access forum but wondered if this was a better location.
We have two Server 2005 boxes and one Sever 2000.
About 3 months ago we started experiencing random database connection drops.
These were manifested by exception errors in database applictions that have worked fine for years.
The .NET application exception message: "The specified network name is no longer available"
The Delphi application exception message: "Possible network error. Write to SQL Server Failed."
Server Management Studio also started giving the error:
"Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"
All three servers randomly lose connections.
All our clients randomly lose connections.
I don't think its a network problem because only db applications seem to be affected.
Also, a client can be running two or more db applications. Application A will fail, while B marches on unaffected.
Does anyone have any ideas what could be causing this?
I would like a way to monitor the SQL Server connections but am not sure how.
sys.sysprocesses seems to show all the connections, but it doesn't show when a connection is being terminated.
SQL Server log shows no connection messages.
Thank you and Help!
That's not going to be easy to track down if it's intermittent and not reproducible. Did you check the SQL Error logs? That would likely show something if it's on the SQL Server end. You can trace connections. You could run a trace or profiler and look at what was executing prior to the connection dropping.
I wouldn't rule out the network or hardware related issues causing problems between the client and the server - I'd suspect that first if there isn't anything in the SQL log. Have you checked all the windows event logs on the server and on the client/server where the application is running? And in particular check for network or hardware related issues? Did you check for DNS issues in your domain?
-Sue
OJ,
Thats an intersting link. However my error messages are not like those described in the article. Additionally the SQL server lives behind a roboust firewall. Finally I have been monitoring connections and activity and have not noticed anyone but our small group on the server.
Thanks for the reply though! 
Synattackprotect is on by default for win2k3 sp1. When this is on, sql will drop connections when there are lots of connections try to connect at the same time. To the system, this is a flood which symbolizes an attack. Also, here is more info on your error number. This very much indicates that you have networking/security problem.
WSAECONNRESET
10054 | - Connection reset by peer.
- An existing connection was forcibly closed by the remote host. This normally results if the peer application on the remote host is suddenly stopped, the host is rebooted, the host or remote network interface is disabled, or the remote host uses a hard close (see setsockopt for more information on the SO_LINGER option on the remote socket). This error may also result if a connection was broken due to keep-alive activity detecting a failure while one or more operations are in progress. Operations that were in progress fail with WSAENETRESET. Subsequent operations fail with WSAECONNRESET.
|
oj at 2007-9-25 >
