SQLBulkCopy JDBC intermittent getting connection reset by peer: socket write error

Question

Hi all,
I am using SQLBulkCopy class from MSSQL JDBC driver mssql-jdbc7.0.0.jre8 to perform synching of table from one database to another database; Some time i am getting exception "java.net.SocketException: Connection reset by peer: socket write error" intermittently especially if the table containing large amount of record E.G 1.1 million with about 40 column

I have set the SQLCopyOptions to have batch size at 3000, and with timeout settings 9000 seconds

what could be the possibility of getting such exception when writting data to the target database and how should I overcome it? should I retry to sync the table again if I received such exception?

my setup

1. mssql-jdbc7.0.0.jre8
2. src DB: MSSQL 2016
3. target DB: MSSQL 2016

Caused by: javax.net.ssl.SSLException: Connection reset
at sun.security.ssl.Alert.createSSLException(Unknown Source) ~[?:1.8.0_261]
at sun.security.ssl.TransportContext.fatal(Unknown Source) ~[?:1.8.0_261]
at sun.security.ssl.TransportContext.fatal(Unknown Source) ~[?:1.8.0_261]
at sun.security.ssl.TransportContext.fatal(Unknown Source) ~[?:1.8.0_261]
at sun.security.ssl.SSLSocketImpl.handleException(Unknown Source) ~[?:1.8.0_261]
at sun.security.ssl.SSLSocketImpl.access$400(Unknown Source) ~[?:1.8.0_261]
at sun.security.ssl.SSLSocketImpl$AppInputStream.read(Unknown Source) ~[?:1.8.0_261]
at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1971) ~[mssql-jdbc-7.0.0.jre8.jar:?]
... 20 more
Suppressed: java.net.SocketException: Connection reset by peer: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method) ~[?:1.8.0_261]
at java.net.SocketOutputStream.socketWrite(Unknown Source) ~[?:1.8.0_261]
at java.net.SocketOutputStream.write(Unknown Source) ~[?:1.8.0_261]
at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyOutputStream.writeInternal(IOBuffer.java:1095) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyOutputStream.write(IOBuffer.java:1088) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at sun.security.ssl.SSLSocketOutputRecord.encodeAlert(Unknown Source) ~[?:1.8.0_261]
at sun.security.ssl.TransportContext.fatal(Unknown Source) ~[?:1.8.0_261]
at sun.security.ssl.TransportContext.fatal(Unknown Source) ~[?:1.8.0_261]
at sun.security.ssl.TransportContext.fatal(Unknown Source) ~[?:1.8.0_261]
at sun.security.ssl.SSLSocketImpl.handleException(Unknown Source) ~[?:1.8.0_261]
at sun.security.ssl.SSLSocketImpl.access$400(Unknown Source) ~[?:1.8.0_261]
at sun.security.ssl.SSLSocketImpl$AppInputStream.read(Unknown Source) ~[?:1.8.0_261]
at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1971) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6369) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7629) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7590) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.doInsertBulk(SQLServerBulkCopy.java:1597) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.access$200(SQLServerBulkCopy.java:63) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy$1InsertBulk.doExecute(SQLServerBulkCopy.java:705) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.sendBulkLoadBCP(SQLServerBulkCopy.java:733) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:1669) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeResultSet(SQLServerBulkCopy.java:641) ~[mssql-jdbc-7.0.0.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:579) ~[mssql-jdbc-7.0.0.jre8.jar:?]

Answer 1

Could be a network problem.

But check out the SQL Server error log if there are any messages in the log that coincide in time with these disconnections.

Answer 2

Hi ErlandSommarskog,
thanks for your reply, when checking the SQL server log, following is captured

Error: 4014, Severity: 20, state: 11
A fatal error occured while reading the input stream from the network. The session will be terminated (input error: -2146893008. output error: 0)

A quick google suggested it might be related to Chimney Offload state is enable; however, for our server, it is not the case

netsh int tcp show global
Receive-Side scaling state: enabled
Chimney offload state: disabled

Answer 3

Well, it is a networking problem, and you may help from networking people to track this down. It's not really my domain, so that I don't have that much to add.

But I might have one clue. The posts that talks about TCP Chimney Error, seem to have input error 10054. Yours have -2146893008. This is the error that SQL Server got when reading the data stream. I lookup this error code in the old Error Lookup utility, and I was told The specified data could not be decrypted.

I can't say why decryption suddenly would fail in the middle of the data stream. And as I said, Error Lookup is old and may not be up to date.