"The semaphore timeout period has expired" SQL Azure

asp.net-mvc azure azure-sql-database entity-framework

Question

I am using Entity Framework 6 to access a SQL Azure database that is running on my.Net MVC Azure Web Site. I occasionally encounter the issue "System.ComponentModel.Win32Exception: The semaphore timeout period has elapsed" (1 in about a thousand queries).

System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) ---> System.ComponentModel.Win32Exception: The semaphore timeout period has expired

Requests made before and after the problem and their interactions with SQL Azure appear to have no apparent cause. Is there a method to deal with or fix this.

1
11
6/13/2014 1:57:46 PM

Accepted Answer

On-premises SQL is considerably different from Azure SQL. A number of connections will be dropped by an overloaded or unavailable Azure SQL Server, and when you reconnect, you will be routed to another SQL Server.

The reason for this problem is that with TCP connections, you can't tell if the other end has cut the connection until you transmit data down it.

The following query will work just fine if your code opens a new connection after recognizing that the previous one has been terminated.

Entity Framework 6 now allows you to interact with Using Entity Framework with SQL Azure for Transient Fault Handling

You must configure and set your SetExecutionStrategy in your DBConfiguration class. To inherit from DbConfiguration, simply create a new class in your project.

public class MyConfiguration : DbConfiguration 
{ 
    public MyConfiguration() 
    { 
        SetExecutionStrategy( 
            "System.Data.SqlClient", 
            () => new SqlAzureExecutionStrategy(1, TimeSpan.FromSeconds(30))); 
    } 
}

Details can be found at Retry logic and Connection Resiliency (EF6 onwards)

15
5/23/2017 12:09:55 PM

Popular Answer



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow