patternsqlModerate
The specified network name is no longer available
Viewed 0 times
theavailablelongernamenetworkspecified
Problem
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.
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.
Solution
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
Example:
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
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 SQLServerNameExample:
172.16.0.5 ProductionSQLBoxThat 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
Code Snippets
SQLServerIPAddress SQLServerName172.16.0.5 ProductionSQLBox<connectionStrings>
<add name="webconfigconnectionString" connectionString="server=SQLServerName;database=dbName;uid=userName;password=ABC123;Connect Timeout=120" />
</connectionStrings>Context
StackExchange Database Administrators Q#149539, answer score: 11
Revisions (0)
No revisions yet.