10

We have an application that access the database (Sql server 2014 enterprise edition). The application calls a stored procedures to access the database. Everything was working fine until recently start sending the following error and stop the applications. Restarting the app fix the issue temporarily but facing the same error latter.

Error: A transport-level error has occurred when receiving results from the server. (Provider: TCP Provider, error: 0 - The specified network name is no longer available.)

I did a lot of research which most of them pointing out as a network issue but could not find anything to actually solve the problem. Do anyone know what change I should have to make on the database side to resolve this issue. I highly appreciate any suggestions.

8

Sounds like spotty DNS resolution issues or long running queries.

For DNS trouble-shooting

As a Band-Aid and trouble-shooting aid add and entry to the hosts file on your app servers (not the SQL Server) at c:\windows\system32\drivers\etc

SQLServerIPAddress SQLServerName

Example:

172.16.0.5 ProductionSQLBox

That way, the SQL Server name will be resolved by the hosts file until you can find the real problem for what's going on with name resolution.

Be sure and test the hosts file by pinging the SQL box from the app server via a command line. Or, alternately create a DSN with the ODBC Administrator within Control Panel and test the connection there.

For long running queries

Implement a longer command timeout value for the application connection strings connecting to the SQL Server.

Example

<connectionStrings> 
    <add name="webconfigconnectionString" connectionString="server=SQLServerName;database=dbName;uid=u‌​serName;password=ABC‌​123;Timeout=120" />
</connectionStrings>
6

This is not a database issue. There is no change on database side that can resolve this issue. This error is caused by high network bandwidth or error in your application. I would suggest you to forward this error to your Wintel/Network team of your company. They can change the network switch or manage the network bandwidth to stop this error.

3

This looks like an application issue.

My opinion is that the application is taking the assumption that the SQL connection is always available (no check or test before running commands) so when the line goes down or anything makes the connection drop/close the application produces that message.

To reproduce, simply kill the SQL session on the SQL server and you will get that very same a similar message.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.