Transport-Level Error in SQL Query Execution Across Environments

Question

A query runs correctly in SSMS when connected to the Synapse development environment, but it throws an error in the production environment. The data volume is the same in both environments for all tables used in the query.

ERROR DETAILS:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

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.)

Answer 1

@Surabhi B N

Welcome to the Microsoft Q&A forum.

The error "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)" in Azure Synapse Analytics, especially when it works in development but not production, points to network connectivity issues, not necessarily data volume differences. Even if the data is the same, the network between your client and the Synapse server might be significantly different.

Here are few steps to troubleshoot and potentially resolve this error:

1. Check Network Stability: Ensure that the network connection between your client and the production server is stable. Network interruptions can cause transport-level errors.
2. Review SQL Server Logs: Look at the SQL Server error logs and Windows Event Viewer on both the client and server machines for any related error messages that might provide more details
3. Timeout Settings: Verify the timeout settings in SQL Server Management Studio (SSMS). Sometimes, increasing the query timeout value can help if the query takes longer to execute
4. NIC Settings: Ensure that the Network Interface Card (NIC) settings on both the client and server are configured correctly. Mismatched settings can lead to network issues
5. DNS Configuration: Check if there are any DNS issues. Sometimes, connecting to the server using its IP address instead of its hostname can bypass DNS-related problems
6. Database Corruption: Run DBCC CHECKDB on your database to check for any corruption. If corruption is found, you may need to restore from a backup or use the repair options
7. Firewall and Antivirus: Ensure that firewalls and antivirus software are not blocking or interfering with the SQL Server connections.

Please refer :

https://learn.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues?view=azuresql#the-serverinstance-was-not-found-or-was-not-accessible-errors-26-40-10053

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you