ASP.NET session state: The timeout period elapsed prior to obtaining a connection from the pool

Question

We are getting this error abruptly in our production environment. Whenever this happens, ASP NET session Db is flooded with the timeout exceptions for a few minutes and then it comes back to normal.

Application configuration: Web form application using ASPState session Db for session management. This session management is on an exclusive DB and we are the only app using this DB. Application has a separate DB for other app schema needs (on the same sql server though).

---

Complete error stack:

Timestamp: 13/07/2017 04:32:35

    Message: Unable to connect to SQL Server session database.

Additional Info: Requested URL[https://www.myAppURL/Services/SessionStateService.asmx/myAction], Browser[Chrome59] Inner Exception: 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. Category: Error&Warning

    Priority: -1

EventId: 0 Severity: Error

    Title:ERROR

Machine: myServer Application Domain: /LM/W3SVC/2/ROOT-1-131443848021611848

    Process Id: 11532

Process Name: c:\windows\system32\inetsrv\w3wp.exe

    Win32 Thread Id: 8236

Thread Name:

    Extended Properties: Stack Trace -    at System.Web.SessionState.SqlSessionStateStore.ThrowSqlConnectionException(SqlConnection conn, Exception e)

at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo, TimeSpan retryInterval) 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.BeginAcquireState(Object source, EventArgs e, AsyncCallback cb, Object extraData) at System.Web.HttpApplication.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) Transaction Id - 9685d278-61dc-4be9-8139-e0bd4131910c

Answer 1

The database server that stores session state is just an ordinary database, subject to all the performance and concurrency limits normally found in a database. In this case, it sounds like either there is too much load or transactions are being blocked by table or page locks.

You can try a couple things.

1. Increase the number of connections, e.g. in the connection string add a "Max Pool Size" attribute and set it higher than the current value. The default is 100.

2. Increase the pace at which stale sessions are cleaned up. Open up the SQL database and find the SQL agent job "delete expired sessions." I believe the default frequency is one minute; try setting it to 30 seconds. If the job cleans up too many sessions at once, it can promote row locks to page or table locks, which will block all other connections until the delete operation is done. Smaller bites are better.

3. Reduce the number of pages that require session state, e.g. set this in web forms:

   <@ Page EnableSessionState="false">
   

   In MVC:

   [SessionState(SessionStateBehavior.Disabled)]
   

   If you can do this on 50% of your pages, you will reduce the SQL load by about 50%.

   If you do not disable session state, even if you don't have any code that uses it in that page, the framework still does the overhead of loading and serializing the session state data at the beginning and end of the pipeline. So you may as well disable it if you're not using it.

4. Make sure you are cleaning up your session variables when you're done with them (e.g. with Session.Remove). If you never clean them up, they just stick around forever, sucking up bandwidth and database I/O. Remember, with out-of-proc session state, 100% of your session state variables get dragged over the wire every single time, whether you are using them or not.

5. If none of the above works, try try reading this article and implementing the solution in there. Basically it's a hack that replaces a Microsoft stored procedure with one that uses a cursor in order to avoid page or table locks. I wouldn't do this except as a last resort.