Loosing Connections to DataBaase

[Former Member]

Good afternoon,

Starting about a month ago some users (random) of our application loose, a few times a day, the connection to the DataBase and they get a (S1000) error. We also have a few applications (agents) just manipulating data, e.g. inserting a row in a table from an xml file. Also these applications sometimes loose the connection. Did not find any logic in time, events a.s.o., it seems random. Personally I think this is a network issue, but i want to (must) exclude the DataBase as an option.

The Database (@@version 12.0.1.4155) is running on a dedicated Server (windows 2008 R2) Users run the application on Citrix The agents run a local application Number of connections ~450 Connection DataBase through ODBC

At this time wireshark is running on a client running an "agent". I can also run it on a citrix server?

Starting a DataBase trace from Sybase Central costs too much performance and i am afraid it won't show the actual error, because it is only tracing/logging "succesfull" requests?

Windows updates have been installed, but more settings have been changed within the network:-(

Moving the DataBase to a new server could be the solution, but any ideas on finding the cause? What must i do to exclude the DataBase, a successfull validition is scheduled weekly.... could it be memory or a lack of memory, consuming queries, too much connections???

Thanks in advance,

Marc

[Former Member]

You can log network activity (connects & disconnects) on the database server by adding the "-z" switch. It will help to identify the abnormal disconnects and whether the client (or network) closed the socket or the disconnect was occurring at the server.

The details are logged in the database server's console log (as specified by your "-o" switch)

The one caveat of using this feature is that it will log much more detail for TDS traffic than you may want. This will only be a concern if you are using Open Client (OCS) or JConnect (JDBC) to connect to the database from one/more applications.

Maybe that will help indicate/vindicate the database server.

[jack_schueler]

Sounds like you have a very large number of connections. What is the typical response like for a client?

Are you familiar with the -tl option? Perhaps this needs to be increased.

You can query the current setting for a connection as follows:

select connection_property('LivenessTimeout');

See http://dcx.sap.com/index.html#sqla170/en/html/3bce6e886c5f1014b6ec8c3ce6dd7115.html.

[Former Member]

Good morning,

thanks for your input! I used the -z option, actually used the online option:

CALL sa_server_option('DebuggingInformation','YES');
CALL sa_server_option('ConsoleLogFile','h:\\LogZ.txt' );

I want to mention that the Sybase ASA Version and OS itself are not changed. I do not think the load has increased abnormail. Must i suspect the DataBase itself or should I investigate other options?

I notice the following error messages in h:\\LogZ.txt: ---Connection terminated abnormally; client socket shut down---

a separate ODBC logging shows:

Thu Dec 15 2016 11:00:57
11:00:57 Attempting to connect using:
UID=narainwi;PWD=*;ServerName=transfusion;CON=SQL_DBC_1108d838;INT=NO;LOG=f:\\inforit\\clarion\\ODBC_159.log;LINKS=TCPIP{};NEWPWD=*
11:00:57 Attempting to connect to a running server...
11:00:57 Attempting TCPIP connection (address 192.168.1.190:2638 found in sasrv.ini cache)
11:00:57 Looking for server with name transfusion
11:00:57 Trying to find server at cached address 192.168.1.190:2638 without broadcasting
11:00:57 Found database server transfusion on TCPIP link
11:00:57 Connected using client address 192.168.1.167:65275
11:00:57 Connected to server over TCPIP
11:00:57 Connected to SQL Anywhere Server version 12.0.1.4155
11:00:57 Application information:
11:00:57 IP=192.168.1.167;HOST=VM-DHCS159;OSUSER=NARAINWI;OS='Windows 2008R2 Build 7601 Service Pack 1';EXE='C:\\Program Files (x86)\\INFORIT\\TransFusion\\importmail.exe';PID=0x59b4;THREAD=0x37b4;VERSION=12.0.1.3769;API=ODBC;TIMEZONEADJUSTMENT=60
11:00:57 Connected to the server, attempting to connect to a running database...
11:00:57 [61920] Connected to database successfully
11:01:04 [61920] Client disconnected
11:01:04 [61920] Disconnected from server
11:05:19 [58775] Connection terminated abnormally; error code 10060
11:05:19 [58775] Communication function i_cs_HandleSQLPresError code 4
11:05:19 [58775] Communication function StrmGetInd code 2
11:05:19 [58775] Client disconnected
LivenessTimeout = 0
IdleTimeout     = 0
Database (@@version 12.0.1.4155) 

Any suggestions?

Regards,

Marc

[Former Member]

notice the error code logged by ODBC

Connection terminated abnormally; error code 10060

[VolkerBarth]

So that ought to be a Windows Socket error (WSAETIMEDOUT)...

[ian_mchardy]

Please note that your ODBC logging snippet refers to two different connections. The connect and disconnect of connection ID 61920, and the abnormal termination of connection ID 58775.

The error code 10060 is the reason for the disconnect, and the description of this winsock error from https://msdn.microsoft.com/en-us/library/windows/desktop/ms740668(v=vs.85).aspx is:

Connection timed out. A connection attempt failed because the connected party did not properly respond after a period of time, or the established connection failed because the connected host has failed to respond.

The reason for the dropped connection from SQL Anywhere's point of view is the TCP/IP connection the client was using got this error from the OS.

FYI I have no idea if this is related or not but using LivenessTimeout = 0 is not recommended in general. Although in this case, it shows that the abnormal disconnect is NOT related to the Liveness Timeout. You may want to increase the timeout from the default of 120 seconds (say to 300 seconds = 5 minutes or 1200 seconds = 20 minutes), but disabling it completely can cause dropped connections to go undetected for hours (until the TCP/IP layer itself detects it).

Kind regards, Ian

[Former Member]

Good evening,

I understand the timeout-part, but might the database be the cause, or the server it is running on. The issue, disconnects, appears on different times. I think the best option is to move the DB to a new server, but is that does not help I am stuck.

Regards,

Marc