C# – Unable to connect to SQL Server session database

asp.netcsession-statesql server

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

Best Answer

It's quite some time since this post was first raised, but I've recently experienced this exact issue in a Production environment and thought it may be useful for others in the future.

We have an ASP.NET MVC web application running on .NET 4.5.2 load-balanced across two nodes. The application was configured to store session in an ASPState database on SQL Server 2012 (version 11.0.5058.0). We were suffering from intermitant timeouts:

Exception information: Exception type: HttpException Exception message: Unable to connect to SQL Server session database. 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.

Some of the answers on here relating to ensuring there are no other connection leaks in the codebase, I believe, are mis-leading. A connection pool is created for each distinct connection string, therefore any fixes to connections to other databases would not have a beneficial effect on the Session State database.

We solved this with two changes:

Raising the Max Pool Size by overriding the default of 100 in the connection string in the web.config file:

  sqlConnectionString="data source=SERVERNAME;Initial Catalog=AspState;user id=AspStateUser;password=xxxxx;App=xxxx; Max Pool Size=200;"

It was apparent that the AspState database had been created with the .NET 2.0 command-line and importantly, the dbo.DeleteExpiredSessions SP has known issues with blocking. To further compound the problem, the job was configured to execute this procedure every minute. The AspState database was re-created using the .NET 4.0 command line as follows:

C:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe -ssadd - sstype c -S OURSERVERNAME -d "AspState" -E

Critically, the later version includes a performance improved version of the DeleteExpiredSessions procedure which incorporates a cursor that deletes expired sessions one at a time. We also changed the schedule of the associated job to execute every hour rather than every minute.

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Best Answer

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Sql – How to return only the Date from a SQL Server DateTime datatype

Related Topic