使用了进程外会话状态与ASP.NET应用程序中的SQL Server连接问题 [英] Connection problems with SQL Server in ASP.NET applications using out-of-process session state

问题描述

我已经部署的4 Windows 2003计算机的一个农场在几个ASP.NET应用程序。每个应用程序使用一个单独的应用程序池和虚拟目录在IIS中。他们在很大程度上依赖于它在单个的SQL Server 2000（＆LT上坚持进程外会议;的sessionState模式=SQLSERVER... /＆GT; ）。应用程序被编译在​​.NET 3.0，但.NET 3.5 SP1安装在服务器上。

I have several ASP.NET applications deployed in a farm of 4 Windows 2003 machines. Each application uses a separate App Pool and Virtual Directory in IIS. They rely heavily on sessions which are persisted out of process on a single SQL Server 2000 (<sessionstate mode="sqlserver" ... />). Applications are compiled against .NET 3.0 but .NET 3.5 SP1 is installed on servers.

每个Web服务器接收约10请求/秒。每隔一段时间，我得到一些例外日志：

Each web server receives approximately 10 requests/second. Every once in a while I get some exceptions in logs:

System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByte()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.SessionState.SqlSessionStateStore.SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData item, Object lockId, Boolean newItem)

或其他

System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.WriteSni()
   at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)
   at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.SessionState.SqlSessionStateStore.SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData item, Object lockId, Boolean newItem)

或另一个：

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByte()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at System.Web.SessionState.SqlSessionStateStore.DoGet(HttpContext context, String id, Boolean getExclusive, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags)

这些错误发生几次，每天要进行约1-2分钟，然后消失。有没有人遇到过这样的问题？还有什么比你的建议我为了进一步追查问题怎么办？对我来说，它看起来更像应用程序的网络问题。难道是SQL服务器无法处理这么多的并发连接上的一些设置？任何建议将不胜AP preciated。

These errors occur a couple of times a day for a period of about 1-2 minutes and then disappear. Has anyone encountered such problems? What could you suggest me to do in order to further track down the problem? To me it looks more like network issues than application. Could it be some settings on the SQL Server which cannot handle so many concurrent connections? Any suggestions would be greatly appreciated.

---

更新：

我通过以减少的对象的数目和尺寸进行主要更新该应用程序被存储在会话解决了这个问题。

I've solved the problem by performing major updates to the application in order to reduce the number and size of the objects being stored in the session.

推荐答案

传输级的错误往往与连接到SQL Server被打破......通常网络。

Transport level errors are often linked to the connection to sql server being broken ... usually network.

过期超时通常时抛出一个SQL查询需要很长时间才能运行。

Timeout Expired is usually thrown when a sql query takes too long to run.

所以，我的联系解决您的SQL Server，然后监视，看看哪些查询超时。

So I would troubleshoot the link to your Sql Server and then monitor to see what queries are timing out.

听起来像是一个SQL作业运行，备份？这可能是锁表或重新启动服务。

Sounds like a SQL job is running, backup? That might be locking tables or restarting the service.

这篇关于使用了进程外会话状态与ASP.NET应用程序中的SQL Server连接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！