Unable to connect to SQL Server session database exception - All pooled connections were in use

Question

I am suddenly getting this error, on production, in my log file:

System.Web.HttpException (0x80004005): Exception of type 'System.Web.HttpException' was thrown.

System.Web.HttpException (0x80004005): Unable to connect to SQL Server session database.

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

I looked at a lot of other answers like here

but I don't have connections leak and I don't want only to set the max pool to 200 or so because I want to understand why I suddenly get this exception...

These are my connection strings:

<!--Nhibernate-->
<add name="name" 
     connectionString="Server= servername;Initial Catalog=DBname;UID=username;Password=password;MultipleActiveResultSets=true" 
     providerName="System.Data.SqlClient" /> 

<!--Entity Framework-->
<add name="name" 
     connectionString= "metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=servername;initial catalog=DBname;user id=userName;password=password;multipleactiveresultsets=True;App=EntityFramework&quot;" 
     providerName="System.Data.EntityClient" /> 

Update

An example of using db without connections leaks:

using (var db = new dbName())
{
    using (var connection = db.Database.Connection)
    {
       var command = connection.CreateCommand();

       ...

       connection.Open();

       using (var reader = command.ExecuteReader())
       {
          ...
          reader.NextResult();
          ...
          reader.NextResult();
          ...
          reader.NextResult();
          ...
       }

       connection.Close();
    }
}  

UPDATE

It turns out that I indeed had a connections leak in Entity Framework, a place that didn't use using, and the connections didn't closed!

Example:

private DbContext context = new DbContext();

...

List<dbObject> SeriesEvents = context.dbObject.Where(e => e.RecurrenceId == entity.RecurrenceId).ToList();

the context variable is not getting closed.

I more thing is that this query made a lot of DB queries more than a 100.

Answer 1

The error message suggests the problem occurs with the session state database but the information you've provided is for the application connections. There is a different pool for session state due to the different connection string.

Common causes for the session state issues are 1) the instance(s) hosting session state database is undersized for the workload or 2) the session state cleanup job causes long-term blocking.

The cleanup job in earlier .NET versions deleted all expired sessions in a single batch and notorious for causing long-term blocking on a busy site, especially when session state is use heavily:

CREATE PROCEDURE dbo.DeleteExpiredSessions
AS
    DECLARE @now datetime
    SET @now = GETUTCDATE()

    DELETE [ASPState].dbo.ASPStateTempSessions
    WHERE Expires < @now

    RETURN 0

Later .NET versions use a cursor for the delete to greatly improve concurrency.

CREATE PROCEDURE dbo.DeleteExpiredSessions
AS
    SET NOCOUNT ON
    SET DEADLOCK_PRIORITY LOW 

    DECLARE @now datetime
    SET @now = GETUTCDATE() 

    CREATE TABLE #tblExpiredSessions 
    ( 
        SessionId nvarchar(88) NOT NULL PRIMARY KEY
    )

    INSERT #tblExpiredSessions (SessionId)
        SELECT SessionId
        FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
        WHERE Expires < @now

    IF @@ROWCOUNT <> 0 
    BEGIN 
        DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
        FOR SELECT SessionId FROM #tblExpiredSessions 

        DECLARE @SessionId nvarchar(88)

        OPEN ExpiredSessionCursor

        FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId

        WHILE @@FETCH_STATUS = 0 
            BEGIN
                DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionId = @SessionId AND Expires < @now
                FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId
            END

        CLOSE ExpiredSessionCursor

        DEALLOCATE ExpiredSessionCursor

    END 

    DROP TABLE #tblExpiredSessions

RETURN 0

If the problem is due to an undersized instance and scale up isn't feasible, consider scaling out using session state partitioning.

Answer 2

Usually this kind of connection pooling issues come with connection leaks. There can be some exceptions happening in DB operations and Connection might not be closed properly. Please add a try{} catch{} finally{} block and close the connection in the finally block.

If you use the using, then the try catch finally is implicit and runtime itself will close the connection. So you need not explicitly close the connection. If using the using block, please remove connection.Close() from your code.

Using statement is actually syntactic sugar for try{} catch{} finally{} where the connection is closed and disposed in finally by runtime.

try
{
     connection.Open();
     // DB Operations
}
finally
{
     connection.Close();                
}