When I am running a long running query or stored procedure on Azure Sql databse, I am getting "A transport-level error has occurred when receiving results from the server" error. I have tried same query from portal as well but

Pankaj Sharma 20

When I am running a long running query or stored procedure on Sql databse, I am getting Msg 64, Level 20, State 0, Line 0 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.) just after 5 minutes. I am getting the same error from application, SSMS and from the Azure portal as well.

I have tried to run the SP from portal query editor but same issue. I don't think it is related to code level timeout issue.

Is there any setting which I have to do on the portal database to increase the query timeout time.

Erland Sommarskog 117.2K Reputation points MVP

2025-02-08T20:16:44.9133333+00:00
Are you saying that this is any code. For instance:

WAITFOR DELAY '00:06:00' SELECT 'Hello!!!'
Vijayalaxmi Kattimani 1,330 Reputation points Microsoft Vendor

2025-02-10T12:45:10.0566667+00:00

Hi @Pankaj Sharma,

Following up to see, we haven’t heard from you on the last response provided by Erland Sommarskog.

In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Thank you,
Vijayalaxmi Kattimani 1,330 Reputation points Microsoft Vendor

2025-02-11T04:58:57.7233333+00:00
Hi @Pankaj Sharma,

As we understand that, you are encountering a query timeout issue with your SQL database.

Here are some steps you can take to address this:

Configure the remote query timeout: You can configure the remote query timeout option in SQL Server by using SQL Server Management Studio or Transact-SQL. This option specifies how long, in seconds, a remote operation can take before SQL Server times out. The default value is 600 seconds (10 minutes), but you can adjust it as needed.

Use SQL Server Management Studio

In Object Explorer, right-click a server and select Properties.

Select the Connections node.

Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for SQL Server to wait before timing out.

Use Transact-SQL

Connect to the Database Engine.

From the Standard bar, select New Query.

Copy and paste the following example into the query window and select Execute. This example shows how to use sp_configure to set the value of the remote query timeout option to 0 to disable the time-out.

USE master; GO EXECUTE sp_configure 'remote query timeout', 0; GO RECONFIGURE; GO

After you configure the remote query timeout option, The setting takes effect immediately without restarting the server.

Troubleshoot query time-out errors: Use tools like Extended Events or SQL Trace to identify the queries that cause the time-out errors and make them run faster.

Please refer to the below mentioned links for more information.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver16

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-query-timeouts

I hope this information helps. Please do let us know if you have any further queries.
Pankaj Sharma 20 Reputation points

2025-02-11T05:26:05.4733333+00:00

Yes, @Erland Sommarskog this is the case. you can see in the attached screen shot when I run the same query it throw error after 5 minutes.

@vVijayalaxmi KattimaniVijayalaxmi Kattimani

USE master; GO EXECUTE sp_configure 'remote query timeout', 0; GO RECONFIGURE; GO

USE master;

GO

EXECUTE sp_configure 'remote query timeout', 0;

GO

RECONFIGURE;

GO

It says "Could not find stored procedure 'sp_configure'."

Please let me know if you want more information.
Erland Sommarskog 117.2K Reputation points MVP

2025-02-11T22:31:24.8533333+00:00

Configure the remote query timeout: You can configure the remote query timeout option in SQL Server by using SQL Server Management Studio or Transact-SQL. This option specifies how long, in seconds, a remote operation can take before SQL Server times out. The default value is 600 seconds (10 minutes), but you can adjust it as needed.

Please, please, this is just so wrong. So completely wrong.

First, Pankaj is using Azure SQL Database, not on-prem SQL Server. You can't run sp_configure in Azure SQL Database (as Pankaj has found out).

And the option in question has absolutely no relation to the issue that Pankaj is running into. This option relates to linked servers. Say that Pankaj had been running a query against Azure SQL Database over a linked server from on-prem. In that case, this setting could matter. (But it would be better to set it on the linked server.)

As for what actually is going on, I don't know. I would be inclined to say that it is some network configuration issue, but if so, it must be inside Azure, since you say that this happens when you run from the portal as well. I'm not very good at Azure networking, but maybe there is something configure on a Vnet. I don't think it is a query timeout per se, but more something that drops a network connection when there has been no traffic for some time.
Vijayalaxmi Kattimani 1,330 Reputation points Microsoft Vendor

2025-02-12T12:03:07.1633333+00:00

Hi @Pankaj Sharma,

Apologies for the inconvenience caused.

@Erland Sommarskog Thank you for clarifying the question.

We would like to inform you that the specified error occurs when the database connection is closed by the database server. However, the connection remains valid in the connection pool of your application. As a result, when you attempt to use the shared connection string to execute a query, it is unable to reach the database.

Here are some links to parallel threads that might be helpful to you:

https://learn.microsoft.com/en-us/answers/questions/370475/sql-server-error-a-transport-level-error-has-occur

https://learn.microsoft.com/en-us/answers/questions/1340346/a-transport-level-error-has-occurred-when-receivin

https://stackoverflow.com/questions/2962927/a-transport-level-error-has-occurred-when-receiving-results-from-the-server

https://community.fabric.microsoft.com/t5/Power-Query/Azure-Transport-Level-Error/td-p/143822

I hope this information helps. Please do let us know if you have any further queries.
Erland Sommarskog 117.2K Reputation points MVP

2025-02-12T19:28:25.9866667+00:00
We would like to inform you that the specified error occurs when the database connection is closed by the database server.

Have you verified that this is the case? I don't know, but it sounds very strange to me. When it comes to on-prem SQL Server, there are certainly a few situation where the server can close the connection:

An error with severity level 20 or higher.

Some one runs the KILL command.

The entire instance is shut down.

In Pankaj's case, the connection was cut even during a WAITFOR command, so none of the points above really seem to fit.

Are you suggesting that the server that hosts the Azure could have a monitoring and kill requests that has been running more than a certain time? I have never heard of such a feature, but if you have, can you please share a reference?

I'm still more inclined to believe that this is somewhere in the network issue. But of course, this could be that I'm less knowledgeable about networking than SQL Server.
Pankaj Sharma 20 Reputation points

2025-02-13T12:43:45.72+00:00

Thanks @Erland Sommarskog for clarify the point and thanks @PratikLad & @Vijayalaxmi Kattimani for your time. I had a discussion with Azure support team regarding this and it took around two days to identify the issue and it seems the issue is at our network end :( . when I was troubleshooting this issue I had run the same query on Azure VM and it was successful over there. It means there was some change in our network and this is a network issue not the SQL or Azure issue. Thank you all again for all your efforts :)
Erland Sommarskog 117.2K Reputation points MVP

2025-02-13T21:45:14.1833333+00:00

Thanks for the update! It was a little puzzling when you said that you got the same error from the portal. Anyway, great to hear that you got it sorted out!
Pankaj Sharma 20 Reputation points

2025-02-14T05:24:52.1466667+00:00

Yes @Erland Sommarskog I was getting same error from Azure portal as well because I was accessing the portal from same network.

Accepted answer

PratikLad 80 Reputation points Microsoft Vendor

2025-02-13T11:57:37.1766667+00:00
Hi @Pankaj Sharma

Agreed With @Erland Sommarskog the issue you are facing is because of network issue.

The root cause of Error Message 64 is typically a transient network issue preventing the application from establishing a connection to the target server. This may be due to temporary network disruptions, firewall restrictions, or server unavailability.

Network settings might block the port 1433 which is used by Azure SQL to connect, switch to another network to connect if possible. Ensure that your network connection is stable.

Ensure the application's connection string is correctly configured in application, specifying the correct port (1433) and the fully qualified server name. Try increasing the connection timeout value in connection string.

As a best practice, ensure that retry logic is implemented to handle transient faults or you retry the connection with better network. For more details, refer to Troubleshoot transient faults and connection errors to SQL Database.

You can use Azure SQL Connectivity Checker it is a PowerShell script will run some connectivity checks from this machine to the server and database to detect and fix a wide variety of connectivity errors.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

When I am running a long running query or stored procedure on Azure Sql databse, I am getting "A transport-level error has occurred when receiving results from the server" error. I have tried same query from portal as well but

0 additional answers

Your answer