SQL Server 2012 connection closed issue

Question

I m getting "com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server returned an incomplete response. The connection has been closed." exception while executing continuous SQL query on Azure SQL database in Java application.SQL query executing on Azure SQL database is fetching data in large amount.While fetching data we do some operation on each row so its take long time to execute.I am getting this exception after certain time period while execution of SQL query(Time period is not consistent).SQL server version is 12.0.2000.8.

Anyone experience this behaviour before?

Code Snippet

 sourceDbConnection = SourceDBConnector().getConnection();
        stmt = createStmt(sourceDbConnection);
        resultSet = stmt.executeQuery(sql query);
        while (resultSet.next()) {
           // .. Do some operation.
        }

SQL query

SELECT coalesce(rfr.UniqueID,rs.UniqueID) as PGUID, rs.resultname, rs.ResultId, rs.modifieddatetime FROM tblResult rs
                JOIN tblResultOrg org ON org.CustomerId = rs.CustomerId
                LEFT OUTER JOIN tblResultFormRef rfr ON rfr.UniqueID = rs.UniqueID
                WHERE org.ORGID IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25) AND NOT EXISTS (SELECT NULL FROM tblSynchedResult srs WHERE srs.ResultId=rs.ResultId) AND rs.Status=1             
                ORDER BY rs.modifieddatetime

Exception:

com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server returned an incomplete response. The connection has been closed.
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1352) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1339) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:3740) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.TDSReader.nextPacket(IOBuffer.java:3687) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.TDSReader.ensurePayload(IOBuffer.java:3663) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.TDSReader.readBytes(IOBuffer.java:3979) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.TDSReader.readWrappedBytes(IOBuffer.java:4001) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.TDSReader.readLong(IOBuffer.java:3971) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.PLPInputStream.makeStream(PLPInputStream.java:73) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.PLPInputStream.makeTempStream(PLPInputStream.java:59) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.PLPInputStream.isNull(PLPInputStream.java:45) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValuePrep(dtv.java:2345) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:2408) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:176) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:113) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:1981) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:1966) ~[sqljdbc4-3.0.0.jar:na]
   at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getString(SQLServerResultSet.java:2291) ~[sqljdbc4-3.0.0.jar:na]
   at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:199) ~[commons-dbcp2-2.1.1.jar:2.1.1]
   at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:199) ~[commons-dbcp2-2.1.1.jar:2.1.1]
   at com.mso.service.etl.job.prepared.EtlPreparedJob.handleFormsHavingNoRecordings(EtlPreparedJob.java:236) ~[classes/:na]
   at com.mso.service.etl.job.prepared.EtlPreparedJob.execute(EtlPreparedJob.java:180) ~[classes/:na]
   at com.mso.service.etl.job.prepared.EtlPreparedJob.run(EtlPreparedJob.java:105) ~[classes/:na]
   at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) [na:1.7.0_85]
   at java.util.concurrent.FutureTask.run(FutureTask.java:262) [na:1.7.0_85]
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_85]
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_85]
   at java.lang.Thread.run(Thread.java:745) [na:1.7.0_85]

score 0 · Answer 1 · edited May 23 '17 at 12:22

There are some thinking for sharing to you.

Accroding to the blog, it seems not to be caused by connecting string without encrypt=true;, but still make sure your jdbc connection enabled using Secure Sockets Layer (SSL) encryption. And the similar issue of the SO thread SQL Server JDBC Error on Java 8: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption is caused by missing SSL encryption.
It seems that the issue was caused by Azure SQL Database resource limits, please see the details at https://azure.microsoft.com/en-us/documentation/articles/sql-database-resource-limits/ and refer to the recommendations of the article to try to solve it. Meanwhile, as reference, please see the article about SQL error codes for SQL Database client applications to figure out the issue。

According to your sql experssion, per my expersion, I suggest that you can try to create a materialized view or a indexed view for the sql query to optimize the performance on Azure SQL, please see https://msdn.microsoft.com/en-us/library/dn589782.aspx, https://msdn.microsoft.com/en-us/library/ms187864(v=SQL.100).aspx and https://msdn.microsoft.com/en-sg/library/ms191432.aspx to know the design pattern.

Peter : no any updates. we are still finding a cause. – Chirag Jul 06 '16 at 09:49 — Chirag, Jul 06 '16 at 09:49

SQL Server 2012 connection closed issue

1 Answers1