Unable to connect to SQL Server session database

Question

My asp.net applications works fine everyday. Until last month, my web start to get 2-3 time of problem with Sqlsession state server as follow:

Blockquote System.Web.HttpException Exception of type 'System.Web.HttpException' was thrown. at System.Web.HttpAsyncResult.End() at System.Web.SessionState.SessionStateModule.EndAcquireState(IAsyncResult ar) at System.Web.HttpApplication.AsyncEventExecutionStep.OnAsyncEventCompletion(IAsyncResult ar) ================================================== Exception: System.Web.HttpException Unable to connect to SQL Server session database. at System.Web.SessionState.SqlSessionStateStore.ThrowSqlConnectionException(SqlConnection conn, Exception e) at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo) at System.Web.SessionState.SqlSessionStateStore.GetConnection(String id, Boolean& usePooling) at System.Web.SessionState.SqlSessionStateStore.DoGet(HttpContext context, String id, Boolean getExclusive, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) at System.Web.SessionState.SqlSessionStateStore.GetItemExclusive(HttpContext context, String id, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags) at System.Web.SessionState.SessionStateModule.GetSessionStateItem() at System.Web.SessionState.SessionStateModule.PollLockedSessionCallback(Object state) ================================================== Exception: 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. at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)

Then I started to look into my session db server and did "exec sp_who" in my sql client that found a lot of records of AspState operation in result.

I don't know what causes this kind of problem because we do changed no serious things in out application, just a few bug fixed.

Here is the detail of my web applications's environments:

asp.net 3.5 (convert from 1.1) ... work very well in 2 servers farm with sqlmode of session state.

Does anyone kwow about this problem or get any idea for investigation? Thanks

Answer 1

I have seen this exact error happen when a developer was using a high number of SqldataReaders to get data to build some sort of a dashboard, but he never closed his data readers even though he had the command behavior set to CloseConnection. Once he did (via enclosure in using blocks) these went away. Again, this may not be the actual cause of your problem, but connection pool exhaustion often points to connections not being closed properly.

Answer 2

Thanks bnkdev, I also have seen this problem with our sql's unreleased connections but for this one, It's about asp.net's SQL Session database (ASPState). The operation of read/write has been done by .net framework.

So I don't know why there's huge number of remain connections. May be there's some processes that lock each other from accessing their session db.

Answer 3

Has your load increased? You might have a lot of connections becuase you have a lot more users using the system.

One thing you can do is optimize your session usage. You can specify if a page uses session state or if it just does a read. This can be a preety big savings if you have pages which don't use or don't modify session.

Answer 4

This really sounds like a situation where you're using up all available connections in the pool. Go through your code and make sure to setup your database connections like this:

using (SqlConnection cn = new SqlConnection(connectionString))
{
    using (SqlCommand cm = new SqlCommand(commandString, cn))
    {
        cn.Open();
        cm.ExecuteNonQuery(); // or fill a dataset, etc.
    }
}

Once out of this "using" statement, the connection will be closed automatically.

Doing this will clean up all the other database connections in your application and the State Server connection will be able to connect when needed.

Answer 5

Dear tongdee,

Did u get the solution? We are also facing the same problem and unable to resolve. We checked the whole code again to confirm unclosed connections/datareaders. Everything is perfect in the coding. Please help.

Answer 6

Not sure if anyone is still looking at this thread, but I found an interesting article about large session state and a large number of concurrent sessions causing problems when the data was being removed (when session is no longer valid). Granted the information here is a bit dated, it may be worth looking into. Here's the link: http://msmvps.com/blogs/greglow/archive/2007/02/04/improving-asp-net-session-state-database-performance-by-reducing-blocking.aspx