Error 10054: [DBNETLIB]ConnectionRead/ConnectionWrite

2985 views
Skip to first unread message

Dat K. AU DUONG

unread,
Aug 25, 2004, 8:26:06 PM8/25/04
to
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.

Kevin McDonnell [MSFT]

unread,
Aug 26, 2004, 1:27:02 PM8/26/04
to
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.

TN

unread,
Sep 2, 2004, 5:04:04 PM9/2/04
to
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

>.
>

Kevin McDonnell [MSFT]

unread,
Sep 2, 2004, 6:09:09 PM9/2/04
to
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.

TN

unread,
Sep 2, 2004, 8:10:34 PM9/2/04
to
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

>.
>

Kevin McDonnell [MSFT]

unread,
Sep 3, 2004, 6:59:56 PM9/3/04
to
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?

TN

unread,
Sep 8, 2004, 11:09:01 PM9/8/04
to
"Kevin McDonnell [MSFT]" <ke...@online.microsoft.com> wrote in message
news:Aa$h$mgkEH...@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.

> 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.



> 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.



> 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.

> 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.

> 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
WCHAR * 0x0012A638 (NYI)
SDWORD * 0x0012A8C8 (10054)
WCHAR * 0x0012A238 [ 76] "[Microsoft][ODBC
SQL
Server Driver][TCP/IP Sockets]ConnectionWrite (send())."
SWORD 511
SWORD * 0x0012A8D2 (76)

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
WCHAR * 0x0012A638 (NYI)
SDWORD * 0x0012A8C8 (11)
WCHAR * 0x0012A238 [ 107] "[Microsoft][ODBC
SQL
Server Driver][TCP/IP Sockets]General network error. Check your network
documentation."
SWORD 511
SWORD * 0x0012A8D2 (107)

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 100
(SQL_NO_DATA_FOUND)
HENV 066F1540
HDBC 066F1F88
HSTMT 066FA478
WCHAR * 0x0012A638 (NYI)
SDWORD * 0x0012A8C8
WCHAR * 0x0012A238
SWORD 511
SWORD * 0x0012A8D2

pfwin 55c-558 ENTER SQLCancel
HSTMT 066FA478

pfwin 55c-558 EXIT SQLCancel with return code 0 (SQL_SUCCESS)
HSTMT 066FA478

pfwin 55c-558 ENTER SQLFreeStmt
HSTMT 066FA478
UWORD 1 <SQL_DROP>

pfwin 55c-558 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 066FA478
UWORD 1 <SQL_DROP>

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 * 0x0DADA688 [ -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 * 0x0DADA688 [ -3] "DELETE FROM
PTWinTran
s WHERE ((fileid = '06006537ECC') AND ((rectype = 2) OR (rectype = 3)))\ 0"
SDWORD -3

DIAG [08S01] [Microsoft][ODBC SQL Server
Driver]Communication li
nk failure (0)

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
WCHAR * 0x0012A638 (NYI)
SDWORD * 0x0012A8C8 (0)
WCHAR * 0x0012A238 [ 61] "[Microsoft][ODBC
SQL
Server Driver]Communication link failure"
SWORD 511
SWORD * 0x0012A8D2 (61)

pfwin 55c-558 EXIT SQLErrorW with return code 100
(SQL_NO_DATA_FOUND)
HENV 066F1540
HDBC 066F1F88
HSTMT 066FA478
WCHAR * 0x0012A638 (NYI)
SDWORD * 0x0012A8C8
WCHAR * 0x0012A238
SWORD 511
SWORD * 0x0012A8D2

pfwin 55c-558 ENTER SQLCancel
HSTMT 066FA478

pfwin 55c-558 EXIT SQLCancel with return code 0 (SQL_SUCCESS)
HSTMT 066FA478

pfwin 55c-558 ENTER SQLFreeStmt
HSTMT 066FA478
UWORD 1 <SQL_DROP>

pfwin 55c-558 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 066FA478
UWORD 1 <SQL_DROP>

pfwin 55c-558 ENTER SQLAllocStmt
HDBC 066F1F88
HSTMT * 0012A720

pfwin 55c-558 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)
HDBC 066F1F88
HSTMT * 0x0012A720 ( 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 * 0x0DADA688 [ -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 * 0x0DADA688 [ -3] "DELETE FROM
PTWinTran
s WHERE ((fileid = '06006537ECC') AND ((rectype = 2) OR (rectype = 3)))\ 0"
SDWORD -3

DIAG [08S01] [Microsoft][ODBC SQL Server
Driver]Communication li
nk failure (0)

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
WCHAR * 0x0012A638 (NYI)
SDWORD * 0x0012A8C8 (0)
WCHAR * 0x0012A238 [ 61] "[Microsoft][ODBC
SQL
Server Driver]Communication link failure"
SWORD 511
SWORD * 0x0012A8D2 (61)

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 100
(SQL_NO_DATA_FOUND
)
HENV 066F1540
HDBC 066F1F88
HSTMT 066FA478
WCHAR * 0x0012A638 (NYI)
SDWORD * 0x0012A8C8
WCHAR * 0x0012A238
SWORD 511
SWORD * 0x0012A8D2

pfwin 55c-558 ENTER SQLCancel
HSTMT 066FA478

pfwin 55c-558 EXIT SQLCancel with return code 0 (SQL_SUCCESS)
HSTMT 066FA478

pfwin 55c-558 ENTER SQLFreeStmt
HSTMT 066FA478
UWORD 1 <SQL_DROP>

pfwin 55c-558 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 066FA478
UWORD 1 <SQL_DROP>

pfwin 55c-558 ENTER SQLAllocConnect
HENV 066F1540
HDBC * 0E0AB37C

pfwin 55c-558 EXIT SQLAllocConnect with return code 0
(SQL_SUCCESS)
HENV 066F1540
HDBC * 0x0E0AB37C ( 0x066f3bd0)

pfwin 55c-558 ENTER SQLSetConnectOption
HDBC 066F3BD0
SQLINTEGER 103 <SQL_LOGIN_TIMEOUT>
SQLPOINTER 0x0000000F

pfwin 55c-558 EXIT SQLSetConnectOption with return code 0
(SQL_SUCCESS)
HDBC 066F3BD0
SQLINTEGER 103 <SQL_LOGIN_TIMEOUT>
SQLPOINTER 0x0000000F (BADMEM)

Kevin McDonnell [MSFT]

unread,
Sep 10, 2004, 1:26:50 PM9/10/04
to
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

Reply all
Reply to author
Forward
0 new messages