Entity Framework, DBContext and "The underlying provider failed on Open" large handshake times

Question

I am having an occasional System.Data.EntityException thrown. The exception indicates a very long handshake time on connection. The exception info is:

System.Data.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=40; handshake=25118; [Login] initialization=0; authentication=0; [Post-Login] complete=4384; ---> System.ComponentModel.Win32Exception: The wait operation timed out

Notice that the "handshake" phase was 25.118 seconds. Given that the connection and command timeouts are only 30, it is not surprising that there is a problem. My questions are.

What could be causing this?
Is there a way to monitor what the connection time is when things are running ok? To narrow down the problem, I'd like to know if it always takes a long time to make a connection or if it's usually very fast and for some reason it occasionally takes more than 30 seconds. It might provide some clues.

I don't want to just increase the connection/command timeouts without knowing a little more. I have read that one can use connection pools, and certainly I'm willing to try that if there is a reason. The database we are using is SQL Express and the code does NOT call dispose on the context, but I read that it is not strictly necessary (example: http://blog.jongallant.com/2012/10/do-i-have-to-call-dispose-on-dbcontext.html). In fact the code is quite similar. We have a class like:

public class MyContext : DbContext
{
   public MyContext (string dbName, bool setInitializer = true)
        : base(dbName)
    {
        if (setInitializer)
        {
            Database.SetInitializer(new MyContextInitializer());

            // Set timeout (based on code from http://stackoverflow.com/questions/6232633/entity-framework-timeouts)
            var adapter = (IObjectContextAdapter) this;
            var objectContext = adapter.ObjectContext;
            objectContext.CommandTimeout = CommandTimeoutSeconds;
        }
    } 

    public DbSet<FuelReading> FuelReadings {get; set;}
}

internal class MyContextInitializer : DropCreateDatabaseIfModelChanges<MyContext>
{
    /// <summary>
    /// Adds initial values to the db on db creation.
    /// </summary>
    /// <param name="context"></param>
    protected override void Seed(MyContext context)
    {
        // Seed 
        // TODO: Remove this seeding if we upgrade to .NET 4.5 (Entity Framework 5 has enum support on .NET 4.5)
        var fuelReading= new fuelReading {Name = "Unknown"};


        context.FuelReadings.Add(fuelReading);


        base.Seed(context);
    }
}

Where I'm seeing an exception is with code like this:

FuelReading reading= (from tz in _dbContext.FuelReadings
                                         where
                                             tz.Id ==
                                             3
                                         select tz).FirstOrDefault();

but I stress that it appears elsewhere as well.

Can I provide any more relevant details? Does anyone have any ideas?

Update. Based on suggestions in the comments and from friends I started looking at PerfMon. Often, the "Connection Reset/sec" goes through the roof (to 300). I can't find a whole lot of information on this particular counter. Taken at face value, it is number of times that connections were reset in a second. Does this imply a lot of connections were attempted or made? I'm not sure why this number would get so high as I think (code is inherited) the database (SQLServer Express) is just written into objects. Those objects are read, manipulated, wrote to, etc. using LINQ, but I didn't think anything with the dB happened again until the all important DbContext.SaveChanges(...), so not sure what this counter is telling me. However, if it really does reflect tons of connections, it might be a big clue as to what is going on. Perhaps I'm out of connections or some such thing?

Too long time, is the database located on the same computer, or is it on a far network? — Tony, Sep 21 '13 at 02:12
there are ways to check ... if query fires to sql then use Log extension method from ef , use tools like glimpse , one is from stackoverflow also there (i forgot name) , use query statements inside using blocks to dispose context.. make sure connection string is right :) — Vishal Sharma, Sep 21 '13 at 17:34
Can you share your anonimized connection string? Is it using an IP address, an netbios name or a fully qulified domain name? Is that box domain joined? Which protocols are enabled? — rene, Sep 21 '13 at 19:08
rene, connection string is: and in constructor for DbContext derived class I have: Database.SetInitializer(new ModalityDataContextInitializer()); var adapter = (IObjectContextAdapter) this; var objectContext = adapter.ObjectContext; objectContext.CommandTimeout = 30; — Dave, Sep 23 '13 at 16:21
vishal, Thanks for the advice. See edits above. I did use PerfMon to examine the problem. — Dave, Sep 23 '13 at 16:28
Have you checked to see if there are any errors in the event log? — TheDaveJay, Sep 27 '13 at 16:34

Entity Framework, DBContext and "The underlying provider failed on Open" large handshake times

0 Answers0