|
|
You're in the
sql server connect
group:Error 10054: [DBNETLIB]ConnectionRead/ConnectionWrite
sql server connect:
Hi,
My company is running: SQL Server 2000 SP3 on Windows 2000 Advanced Server SP4 System Config: 4 Processor 2 GB Memory 80 GB Compaq SCSI Drive (Raid 4). we have recently apply SQL 2000 SP3 and have encountered the following error on the client applications: 1. [Microsoft][ODBC SQL Server Driver]Communication Link Failure 2. [Microsoft SQL Server: 10054][Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite(send()).Microsoft SQL Server:11 ... 3. [Microsoft SQL Server: 10054][Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite(read()) ... Does Anyone Encounter this problem or know how to fix this please help! Notice: The problem occurred on more than 1 machine, randomly. Problem happen on application that connect to the database. I have Check: 1. Win 2000 Server - Application Log (No Error) 2. SQL 2000 Server - Error Log (No Error) 3. Client - Application Log (No Error) 4. I have checked with the Network Support Staff and no problem have been found. 5. I have try running profiler on the client, it also drop out with one of the above error. 6. I have checked on the internet, everyone was pin point this error 10054 is network error, but no one have said how to solve it. Therefore It seem like something in between reseted the tcp/ip communication: Application - OLEDB/ODBC Driver - Client Side Net Lib - TCP/IP Sockects|Net Lib Router <---- TCP/IP Network ----> TCP/IP Sockets|Net Lib Router - Server Side Net Lib - SQL Server Database Engine. Any idea? what should be my approach to solve this problem. Thanks in advance. Regards Dat.
10054 is generally associated with a drop in the underlying network
session. Make a network trace from the client, and you should see either a Reset packet or Fin packet. More than likely this is caused by a TCP reset on the network. Work with your network team to resolve this. Thanks, Kevin McDonnell Microsoft Corporation This posting is provided AS IS with no warranties, and confers no rights.
Kevin,
I am experiencing a very similar problem (unfortunately I don't have the error code since the application doesn't spit it out). I am using Win2K SQL server SP3a and MDAC 2.8 on the client. Now, I did a detailed packet trace of the TCP connection between the client and the server. The client seems to connect fine to the server and do some operations. Then usually there is a period of inactivity (you can see the keepalives and acks for them). Then the client issues a RST for the TCP connection and this is usually correlated with initiating some new activity on the application. At this point, the application declares "communication link failure" and the user usually has to restart the application. The above scenario is the most common, but there are other situations where you can see a TCP RST from the client all of a sudden. Could this be related some sort of connection pooling problem? This occurs very randomly and is hard to debug. Any suggestions on what else can be done to debug this further? I have extensive packet traces and I can get more traces if necessary. I really want to get to the bottom of this though. Thanks, TN [quoted text, click to view] >-----Original Message-----
>10054 is generally associated with a drop in the underlying network >session. Make a network trace from the client, and you should see either a >Reset packet or Fin packet. More than likely this is caused by a TCP reset >on the network. Work with your network team to resolve this. > >Thanks, > >Kevin McDonnell >Microsoft Corporation > >This posting is provided AS IS with no warranties, and confers no rights. > > > >.
Yes, that is a million dollar qn, no doubt!
I have simulated the problem of network failure several times and we get the same CLF problem, but the footprint is different. For example, if any link in the middle goes down (e.g. pull connxn on a switch), then you can see the 5 exponentially backed off retransmissions on the wire and the eventual timeout of the connection. But that is just not what is happening here. I have nearly 50 traces to prove it :) So, is there some sort of tracing that can be enabled in the ODBC itself? I would like to find out if the application is initiating the reset or if the ODBC is initiating it and if there is some clue as to why it is initiating this. Another interesting thing is that there are also netbios-ssn connections to the SQL server from the client to the SQL server and there are similar resets initiated from the client at around the same time. We are not used named pipes, only TCP/IP. And also, the ODBC settings have connection pooling disabled. However, the application can potentially enable it via the API, right? I will go ahead and try increasing the retransmission value, and will report back on it. Thanks for the feedback, Ajit [quoted text, click to view] >-----Original Message-----
>The million dollar question is to try to determine why the reset occurs. >From my experience there is never one answer. > >It could be that the tcp session didn't reply back for an ack, or it could >be caused by a misconfigured switch on the network. > >You may want to try adjusting the TcpMaxDataRetransmissions value in the >registry on the client. > >HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters >Value Name: TcpMaxDataRetransmissions > >The default value is 5. Try adjusting it up. > > >Thanks, > >Kevin McDonnell >Microsoft Corporation > >This posting is provided AS IS with no warranties, and confers no rights. > > > >.
The million dollar question is to try to determine why the reset occurs.
From my experience there is never one answer. It could be that the tcp session didn't reply back for an ack, or it could be caused by a misconfigured switch on the network. You may want to try adjusting the TcpMaxDataRetransmissions value in the registry on the client. HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters Value Name: TcpMaxDataRetransmissions The default value is 5. Try adjusting it up. Thanks, Kevin McDonnell Microsoft Corporation This posting is provided AS IS with no warranties, and confers no rights.
You can enable ODBC tracing, but it is very verbose. Also, it's not
timestamped so, it will be hard to match it up with a network trace. I'm checking on some additional tracing tools for you. You may want to implement some application level tracing as well. ODBC will initiate a session close or Fin with a Connection Close call. There's nothing specific in the ODBC API that would result in a tcp reset though. Is there any hardware between the client machine and the server? Can you reproduce the same problem in a lab environment? Do you see the tcp reset on the client or on the server? Thanks, Kevin McDonnell Microsoft Corporation This posting is provided AS IS with no warranties, and confers no rights.
[quoted text, click to view]
"Kevin McDonnell [MSFT]" <kevmc@online.microsoft.com> wrote in message news:Aa$h$mgkEHA.3852@cpmsftngxa10.phx.gbl... > You can enable ODBC tracing, but it is very verbose. Also, it's not > timestamped so, it will be hard to match it up with a network trace. > I'm checking on some additional tracing tools for you. That will be great. [quoted text, click to view] > You may want to implement some application level tracing as well. I've asked the application company if there any hooks for enabling application level tracing, but there seem to be none :( In any case, I managed to get the ODBC traces and yes it is very very verbose (produced 650 MB of data). I will post a portion of the trace where the error occurs after I respond to your questions. [quoted text, click to view] > ODBC will initiate a session close or Fin with a Connection Close call. > There's nothing specific in the ODBC API that would result in a tcp reset > though. I figured. I couldn't find anything in there to suggest that either. [quoted text, click to view] > Is there any hardware between the client machine and the server? The network config is as follows: client - <switch> - <router> - frame relay cloud - <router> - <switch> - SQL server All LAN connections are 100 Mbps, FR cloud is 384 Kbps. I've monitored all traffic on the FR link, and there are periodic retransmissions, but otherwise the link utilization is pretty low. [quoted text, click to view] > Can you reproduce the same problem in a lab environment? No - this problem happens only with the remote sites, interestingly. There are a couple of users for whom this happens on a daily basis (a couple of times at least) and I have been monitoring them with whatever tools I have. [quoted text, click to view] > Do you see the tcp reset on the client or on the server? I see the reset being sent by the client to the server by putting a sniffer on the wire at the server end. My next step is to put a sniffer on the wire at the client end as well and see what I can glean. Now, here is a ODBC trace of the problem occurring. It looks like there is some disconnect activity, then an SQLExecDirect which fails and then a diag code indicating the CLF. Any clues from this? There are a couple more instances where this same error shows up, but nothing to suggest what is initiating the error. Is it a server disconnect, local TCP/IP problem, or ....? pfwin 55c-558 EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 066FA478 pfwin 55c-558 ENTER SQLFetch HSTMT 066FA478 pfwin 55c-558 EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 066FA478 pfwin 55c-558 ENTER SQLFetch HSTMT 066FA478 pfwin 55c-558 EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 066FA478 pfwin 55c-558 ENTER SQLFetch HSTMT 066FA478 pfwin 55c-558 EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 066FA478 pfwin 55c-558 ENTER SQLFetch HSTMT 066FA478 pfwin 55c-558 EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 066FA478 pfwin 55c-558 ENTER SQLFetch HSTMT 066FA478 pfwin 55c-558 EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 066FA478 pfwin 55c-558 ENTER SQLFetch HSTMT 066FA478 pfwin 55c-558 EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND) HSTMT 066FA478 pfwin 55c-558 ENTER SQLFreeHandle SQLSMALLINT 3 <SQL_HANDLE_STMT> SQLHANDLE 066FA478 pfwin 55c-558 EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS) SQLSMALLINT 3 <SQL_HANDLE_STMT> SQLHANDLE 066FA478 pfwin 55c-558 ENTER SQLDisconnect HDBC 066F3BD0 pfwin 55c-558 EXIT SQLDisconnect with return code 0 (SQL_SUCCESS) HDBC 066F3BD0 pfwin 55c-558 ENTER SQLFreeHandle SQLSMALLINT 2 <SQL_HANDLE_DBC> SQLHANDLE 066F3BD0 pfwin 55c-558 EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS) SQLSMALLINT 1 <SQL_HANDLE_ENV> SQLHANDLE 066F84F8 pfwin 55c-558 ENTER SQLAllocStmt HDBC 066F1F88 HSTMT * 0012A938 pfwin 55c-558 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS) HDBC 066F1F88 HSTMT * 0x0012A938 ( 0x066fa478) pfwin 55c-558 ENTER SQLSetStmtOption HSTMT 066FA478 UWORD 0 <SQL_QUERY_TIMEOUT> SQLPOINTER 0x00000000 pfwin 55c-558 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS) HSTMT 066FA478 UWORD 0 <SQL_QUERY_TIMEOUT> SQLPOINTER 0x00000000 pfwin 55c-558 ENTER SQLExecDirect HSTMT 066FA478 UCHAR * 0x0DADA5F8 [ -3] "DELETE FROM PTWinTran s WHERE ((fileid = '06006537ECC') AND ((rectype = 2) OR (rectype = 3)))\ 0" SDWORD -3 pfwin 55c-558 EXIT SQLExecDirect with return code -1 (SQL_ERROR) HSTMT 066FA478 UCHAR * 0x0DADA5F8 [ -3] "DELETE FROM PTWinTran s WHERE ((fileid = '06006537ECC') AND ((rectype = 2) OR (rectype = 3)))\ 0" SDWORD -3 DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] ConnectionWrite (send()). (10054) DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] General network error. Check your network documentation. (11) pfwin 55c-558 ENTER SQLErrorW HENV 066F1540 HDBC 066F1F88 HSTMT 066FA478 WCHAR * 0x0012A638 (NYI) SDWORD * 0x0012A8C8 WCHAR * 0x0012A238 SWORD 511 SWORD * 0x0012A8D2 pfwin 55c-558 EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV 066F1540 HDBC 066F1F88 HSTMT 066FA478
Right. You trace indicates that we're unable to write data back to the
server. pfwin 55c-558 ENTER SQLExecDirect HSTMT 066FA478 UCHAR * 0x0DADA5F8 [ -3] "DELETE FROM PTWinTran s WHERE ((fileid = '06006537ECC') AND ((rectype = 2) OR (rectype = 3)))\ 0" SDWORD -3 pfwin 55c-558 EXIT SQLExecDirect with return code -1 (SQL_ERROR) HSTMT 066FA478 UCHAR * 0x0DADA5F8 [ -3] "DELETE FROM PTWinTran s WHERE ((fileid = '06006537ECC') AND ((rectype = 2) OR (rectype = 3)))\ 0" SDWORD -3 DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] ConnectionWrite (send()). (10054) This error 10054, means that the client connection reset the connection more than likely. A network trace on the client side would confirm this. If you were to execute the same statement from ISQL.exe would the statement complete? (ISQL.exe) uses dblibrary and doesn't go thru ODBC. Since the problem only happens remotely, I would doublecheck the routers and switches in between. Check all the duplex settings. This can cause intermittant failures. See Cisco's document: http://www.cisco.com/en/US/tech/tk389/tk214/technologies_tech_note09186a0080 094781.shtml Configuring and Troubleshooting Ethernet 10/100/1000Mb Half/Full Duplex Auto-Negotiation Thanks, Kevin McDonnell Microsoft Corporation This posting is provided AS IS with no warranties, and confers no rights.
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |