You get "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" error when connecting to a mirrored database

Article ID: 2555235 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

When connecting to a Microsoft SQL Server mirrored database using Sqlclient provider, you may intermittently receive the following error message:

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.LoginWithFailover(Boolean useFailoverHost, ServerInfo primaryServerInfo, String failoverHost, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)

If your application is configured to use the default value for the ConnectionTimeout property (15 seconds), you will receive this error message at 1.2 seconds, when running into the issue that is described in this article.

CAUSE

As per the Connection Retry Algorithm for Mirroring, SqlClient code takes the connection timeout (as specified in the ConnectionTimeout property) and multiplies it by 0.08 (8%) to determine the time it has to wait for first read (SniReadSync) call to the back end SQL server to complete. If the first SniReadSync call fails to complete within that time - either due to a slow response from the server or network latency, the connection is incorrectly set to a doomed state by the SqlClient provider. This causes the connection attempt to prematurely fail with the error message that is noted in the Symptoms section of the article. For additional details refer to the More Information section of this article.

Status:

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section of this article. This issue is scheduled to be addressed in a future version of the .Net Sqlclient provider and this article will be updated to reflect the new information at that time.

RESOLUTION

Resolution of the problem requires you to diagnose and resolve the underlying problems that are contributing to the slowness.
  • If the slowness is due to network latency, you can try the following to improve the network performance by doing the following:
    • Update your NIC drivers to the latest firmware version. Microsoft Support has seen network latency when your NIC card is not compatible with certain Scalable Networking Pack settings.
    • If you are on Windows Vista SP1 or above you may also consider disabling Receive Window Auto-Tuning.
    • If NIC Teaming is enabled on the SQL server, temporarily disable Teaming to see whether the network latency issue is resolved. If it does make a difference take the following steps before re-enabling NIC teaming:
      • Update the NIC drivers to the latest available from the NIC manufacturer.
      • Strictly follow the Teaming configuration recommendations of the NIC manufacturer.
      • Contact the hardware manufacturer for additional troubleshooting with NIC teaming issues.

  • If the issue is due to poorly performing SQL Server, steps should be taken to improve the performance. For additional information refer to the following links:

WORKAROUND

You can use one of the following methods to workaround the problem while you work on implementing long term solutions discussed in the Resolution section:
  • Increase the connection string timeout to 150 sec. This will cause the Sqlclient provider to wait for 12 ( 150* .08=12) seconds for the first read call to complete – thereby giving additional time for the connection to stay alive and prevent the error from happening too soon.
  • Add "Min Pool Size=20" (the default is 0) in the connection string. This will always maintain a minimum of 20 connections in the pool thereby increasing the chances of the application reusing an existing connection and succeeding rather than trying to open a new connection and failing with the error messages discussed in the Symptoms section of this article.

MORE INFORMATION

The following screenshot shows a network capture between the application server and SQL Server at the time the problem was happening in a test scenario:

Collapse this imageExpand this image
2555234


In this example we can see that the App server closes the connection (Frame 670 AF packet) close to 1 sec during the initial connection phase (TDS:Prelogin). When the SQL Server responses back in 5 sec it is too late and client will reset the connection. As explained in the Cause section, when Connection TimeOut is set to 15 seconds (default value), actual Connection Timeout calculated by the Connection retry algorithm for mirroring is equal to 1.2 sec ((.08*15).

A BID trace that was captured from the test App server shows the following:

<prov.DbConnectionHelper.ConnectionString_Set|API> 1#, 
'Data Source=MySQLServer;Failover Partner=MySQLServer2; Initial Catalog= TestMirror;Integrated Security=SSPI;Connection Timeout=15'
enter_02 <SNIReadSync|API|SNI> 2#{SNI_Conn}, pConn: 007D9EA8{SNI_Conn*}, ppNewPacket: 0671E268{SNI_Packet**}, timeout: 1134
enter_03 <Np::ReadSync|API|SNI> 3#, ppNewPacket: 0671E268{SNI_Packet**}, 
iTimeOut: 1134<SNI_Packet::SNIPacketAllocateEx2|API|SNI> pConn: 007D9EA8{SNI_Conn*}, IOType: 0, 
consumer: 0<SNI_Packet::SNIPacketNew|API|SNI> pConn: 007D9EA8{SNI_Conn*}, IOType: 0ObtainIDa 5# 
<SNI_Packet::SNI_Packet|ID|SNI> 0624EEF0{.}<SNI_Packet::SNI_Packet|SNI> 5#{SNI_Packet} created by 2#{SNI_Conn}
<SNI_Packet::SNIPacketNew|RET|SNI> 0624EEF0{SNI_Packet*}
<SNI_Packet::SNIPacketAllocateEx2|RET|SNI> 0624EEF0{SNI_Packet*}
<Np::ReadSync|ERR|SNI> ProviderNum: 4{ProviderNum}, SNIError: 11{SNIError}, 
NativeError: 258{WINERR}<Np::ReadSync|RET|SNI> 258{WINERR}leave_03<SNIReadSync|RET|SNI> 258{WINERR}, 
Packet: 00000000leave_02<sc.SqlError.SqlError|ERR> infoNumber=-2, errorState=0, errorClass=11, 
errorMessage='Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.', 
procedure='', lineNumber=0<sc.SqlInternalConnectionTds.BreakConnection|RES|CPOOL> 4#, 
Breaking connection.<prov.DbConnectionInternal.|RES|INFO|CPOOL> 4#, 
Dooming<sc.SqlInternalConnectionTds.LoginFailure|RES|CPOOL> 4# DoomThisConnection|RES|INFO|CPOOL> 4#, 
Dooming<sc.SqlInternalConnectionTds.LoginFailure|RES|CPOOL> 4#
Similarly when you review the Connectivity ring buffer for Login Timers on the SQL Server, you will notice that the entire login process got timed out in a short timeframe of milliseconds.

You can run the following query to get the ring buffer output from Management Studio:

SELECT CAST(record AS XML) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2555235 - Last Review: December 19, 2011 - Revision: 4.0
APPLIES TO
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Enterprise Evaluation
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
Keywords: 
KB2555235

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com