C# 无法连接到 SQL Server 会话数据库

Unable to connect to SQL Server session database

提问by tongdee

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:

我的 asp.net 应用程序每天都运行良好。直到上个月，我的网络开始出现 2-3 次 Sqlsession 状态服务器的问题，如下所示：

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)

Blockquote System.Web.HttpException 类型“System.Web.HttpException”的异常被抛出。在 System.Web.HttpAsyncResult.End() 在 System.Web.SessionState.SessionStateModule.EndAcquireState(IAsyncResult ar) 在 System.Web.HttpApplication.AsyncEventExecutionStep.OnAsyncEventCompletion(IAsyncResult ar) ============ ====================================== 异常：System.Web.HttpException 无法连接到 SQL服务器会话数据库。在 System.Web.SessionState.SqlSessionStateStore.ThrowSqlConnectionException(SqlConnection conn, Exception e) 在 System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo) 在 System.Web.SessionState.SqlSessionStateStore.GetConnection(String id, Boolean& usePooling ) 在 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) 在 System.Web.SessionState.SessionStateModule.GetSessionStateItem() 在 System.Web.SessionState.SessionStateModule.PollLockedSessionCallback(Object state) ====================== ============================ 异常：System.InvalidOperationException 超时。在从池中获取连接之前超时时间已过。这可能是因为所有池连接都在使用中并且达到了最大池大小。在 System.Data.ProviderBase。

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.

然后我开始查看我的会话数据库服务器，并在我的 sql 客户端中执行了“exec sp_who”，结果发现了很多 AspState 操作的记录。

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:

以下是我的 Web 应用程序环境的详细信息：

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

asp.net 3.5（从 1.1 转换）...在会话状态为 sqlmode 的 2 个服务器场中工作得很好。

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

有没有人知道这个问题或对调查有任何想法？谢谢

回答by Ta01

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.

当开发人员使用大量 SqldataReaders 来获取数据以构建某种仪表板时，我已经看到这种确切的错误发生，但他从未关闭他的数据读取器，即使他将命令行为设置为 CloseConnection。一旦他这样做了（通过使用块的外壳）这些就消失了。同样，这可能不是问题的实际原因，但连接池耗尽通常表示连接未正确关闭。

回答by JoshBerke

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.

您可以做的一件事是优化会话使用。您可以指定页面是使用会话状态还是仅进行读取。如果您有不使用或不修改会话的页面，这可能是一个非常大的节省。

回答by grogan31

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.

这样做将清除应用程序中的所有其他数据库连接，并且状态服务器连接将能够在需要时进行连接。

回答by ctc

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

不确定是否有人还在看这个线程，但我发现了一篇有趣的文章，关于大型会话状态和大量并发会话在删除数据时导致问题（当会话不再有效时）。鉴于这里的信息有点过时，可能值得研究。这是链接：http: //msmvps.com/blogs/greglow/archive/2007/02/04/improving-asp-net-session-state-database-performance-by-reducing-blocking.aspx

回答by abrown

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:

我们有一个 ASP.NET MVC Web 应用程序在 .NET 4.5.2 上运行，在两个节点之间进行负载平衡。该应用程序被配置为将会话存储在 SQL Server 2012（版本 11.0.5058.0）上的 ASPState 数据库中。我们受到间歇性超时的困扰：

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.

异常信息：异常类型：HttpException 异常消息：无法连接到 SQL Server 会话数据库。超时已过。在从池中获取连接之前超时时间已过。这可能是因为所有池连接都在使用中并且达到了最大池大小。

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:

我们通过两个更改解决了这个问题：

1. 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;" 
   

2. 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:

1. 通过覆盖 web.config 文件中连接字符串中的默认值 100 来提高最大池大小：

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

2. 很明显，AspState 数据库是使用 .NET 2.0 命令行创建的，重要的是，dbo.DeleteExpiredSessions SP 存在已知的阻塞问题。为了进一步加剧问题，该作业被配置为每分钟执行一次此过程。AspState 数据库是使用 .NET 4.0 命令行重新创建的，如下所示：

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

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.

至关重要的是，更高版本包括性能改进版本的 DeleteExpiredSessions 过程，该过程包含一个游标，一次删除一个过期会话。我们还将相关作业的时间表更改为每小时而不是每分钟执行一次。

回答by Nitin A.

If none of the answers here work, then check out connectionString in <configuration>and <sessionState>. In my case I updated the connection string in <configuration>but not in session state to point to a different SQL server.

如果这里的答案都不起作用，请检查 connectionString in<configuration>和<sessionState>。在我的情况下，我更新了连接字符串<configuration>但不在会话状态中以指向不同的 SQL 服务器。

Simply specifying the correct connection string in <sessionState>resolved this same error.

只需指定正确的连接字符串即可<sessionState>解决同样的错误。