none
数据库连接问题 RRS feed

  • 问题

  • 客户端、运行SQL Server的服务器: Windows Server 2008 (x64)

    SQL Server: SQL Server 2008 R2 SP2

    命名实例、SQL Server Browser 服务开启、操作系统防火墙完全关闭。

    sysprocesses视图,最多也就几十行记录。

    WEB服务器和SQL Server 位于网络负载很低的千兆局域网内。

    偶尔出现以下错误:

    System.Web.HttpException (0x80004005): Unable to connect to SQL Server session database. ---> System.Data.SqlClient.SqlException (0x80131904): 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() 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.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) 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, TimeSpan retryInterval) 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.ResetItemTimeout(HttpContext context, String id) 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)

    目前这种连接问题每天会出现两三次,绝大多数时间运行正常。大家有没有遇到过这样的问题,或者有什么好的排查建议。谢谢。

    2012年8月21日 6:17

全部回复

  • 可以先排查一下网络原因,建议使用Fping这个命令工具,它可以记录时间点并且将结果存到一个log文件中。

    另外,从sql server log中是否有什么有价值的信息?

    2012年8月21日 9:20
  • You can query ring buffer to get connection info at specific time.
    2012年8月22日 0:11
  • ring buffer information :


        <SniConsumerError>17830</SniConsumerError>
        <SniProvider>7</SniProvider>
        <State>11</State>

    <TdsBuffersInformation>
          <TdsInputBufferError>10054</TdsInputBufferError>
          <TdsOutputBufferError>0</TdsOutputBufferError>
          <TdsInputBufferBytes>0</TdsInputBufferBytes>
    </TdsBuffersInformation>

    <LoginTimers>
          <TotalLoginTimeInMilliseconds>0</TotalLoginTimeInMilliseconds>
          <LoginTaskEnqueuedInMilliseconds>0</LoginTaskEnqueuedInMilliseconds>
          <NetworkWritesInMilliseconds>0</NetworkWritesInMilliseconds>
          <NetworkReadsInMilliseconds>0</NetworkReadsInMilliseconds>
          <SslProcessingInMilliseconds>0</SslProcessingInMilliseconds>
          <SspiProcessingInMilliseconds>0</SspiProcessingInMilliseconds>
          <LoginTriggerAndResourceGovernorProcessingInMilliseconds>0</LoginTriggerAndResourceGovernorProcessingInMilliseconds>
    </LoginTimers>

    thx for your reponse

    2012年8月23日 1:39
  • Log里面没有任何关于连接的记录,网络应该没有问题的。

    Rmiao说的,我在ring buffer 确实找到了连接超时的信息,但是不明白应该怎样定位问题所在。

    2012年8月23日 1:42
  • Log里面没有任何关于连接的记录,网络应该没有问题的。

    Rmiao说的,我在ring buffer 确实找到了连接超时的信息,但是不明白应该怎样定位问题所在。

    log里没有记录,不代表网络没有问题,因为它跟本没到记log这这一步呢。
    2012年8月23日 7:21
  • Several things to check:

    1. did server have free scheduler?

    2. was server busy around that time?

    3. did you mirror db? There's .net bug on mirrored db connection.

    2012年8月23日 15:50