无法连接到SQL Server会话数据库 [英] Unable to connect to SQL Server session database
问题描述
我的asp.net应用程序每天都能正常运行.直到上个月，我的网站开始出现2-3次Sqlsession状态服务器的问题，原因是遵循:
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:
BlockquoteSystem.Web.HttpException引发了类型为'System.Web.HttpException'的异常.在System.Web.HttpApplication.AsyncEventExecutionStep.OnAsyncEventCompletion(IAsyncResult ar)的System.Web.SessionState.SessionStateModule.EndAcquireState(IAsyncResult ar)的System.Web.HttpAsyncResult.End()处=====================================异常: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&在System.Web.SessionState.SqlSessionStateStore.DoGet(useContext)上获取System.Web.SessionState.SqlSessionStateStore.GetItemExclusive(HttpContext)在System.Web.SessionState.SessionStateModule.GetSessionStateItem()的System.Web.SessionState.SessionStateModule.GetSessionStateItem()处的上下文上下文，字符串ID，布尔值和锁定状态，TimeSpan& lockAge，Object& LockId，SessionStateActions和actionFlags)(对象状态)================================================例外:系统.InvalidOperationException超时时间已到.从池中获取连接之前已经经过了超时时间.发生这种情况的原因是所有池化连接都在使用中，并且达到了最大池大小.在System.Data.SqlClient.SqlConnection.Open()的System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection externalConnection，DbConnectionFactory connectionFactory)的System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)处.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)
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)
然后，我开始查看会话数据库服务器，并在sql客户端中执行了"exec sp_who"，结果发现很多AspState操作的记录.
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.
我不知道是什么原因导致这种问题的发生，因为我们没有做任何认真的事情在out应用程序中，仅修复了一些错误.
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.
这是我的Web应用程序环境的详细信息:
Here is the detail of my web applications's environments:
asp.net 3.5(从1.1转换)...在2个服务器场使用会话状态的sqlmode.
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
推荐答案
自从这篇文章被提出以来已经有一段时间了，但是我最近在生产环境中遇到了这个确切的问题，并且认为它可能对其他人有用.未来.
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.
我们有一个在.NET 4.5.2上运行的ASP.NET MVC Web应用程序，它在两个节点之间实现了负载平衡.该应用程序已配置为将会话存储在SQL Server 2012(版本11.0.5058.0)上的ASPState数据库中.我们遭受间歇性超时的困扰:
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:
异常信息:异常类型:HttpException异常消息:无法连接到SQL Server会话数据库.超时时间已到.从池中获取连接之前已经经过了超时时间.发生这种情况的原因可能是所有池化连接都在使用中，并且达到了最大池大小.
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:
-
通过在web.config文件的连接字符串中覆盖默认值100来增加最大池大小:
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;"
很明显，AspState数据库是使用.NET 2.0命令行创建的，重要的是dbo.DeleteExpiredSessions SP存在阻塞问题.为了使问题更加复杂，该作业已配置为每分钟执行一次此过程.如下使用.NET 4.0命令行重新创建了AspState数据库:
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
C:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe -ssadd - sstype c -S OURSERVERNAME -d "AspState" -E
重要的是，更高版本包括DeleteExpiredSessions过程的性能改进版本，该过程合并了一个游标，该游标一次删除一个过期的会话.我们还更改了关联作业的时间表，使其每小时执行一次，而不是每分钟执行一次.
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.
这篇关于无法连接到SQL Server会话数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
