Article ID: 2605597 - Last Review: April 27, 2012 - Revision: 5.0

FIX: Time-out error when a mirrored database connection is created by the .NET Framework data provider for SQLClient

View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

On This Page

Expand all | Collapse all

SYMPTOMS

When an application uses the Microsoft .NET Framework 3.5 or Microsoft .NET Framework 4 data provider for Microsoft SQL Server (SQLClient) to connect to a mirrored database, you may 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)
Note If the ConnectionTimeout property is set to the default value of 15 seconds, you may receive the error message after the connection is open for 1.2 seconds.
Back to the top

CAUSE

This issue occurs because of an error in the connection-retry algorithm for mirrored databases.

When the retry-algorithm is used, the data provider waits for the first read (SniReadSync) call to finish. The call is sent to the back-end computer that is running SQL Server, and the waiting time is calculated by multiplying the connection time-out value by 0.08. However, the data provider incorrectly sets a connection to a doomed state if a response is slow and if the first SniReadSync call is not completed before the waiting time expires.

Note The slow response in this case may be triggered either by the server or by network latency.
Back to the top

RESOLUTION

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing the problem described in this article. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft website:
http://support.microsoft.com/contactus/?ws=support (http://support.microsoft.com/contactus/?ws=support)
Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Prerequisites

There are two hotixes for this issue. One hotfix applies to the .NET Framework 3.5.1 and one hotfix applies to the .NET Framework 4.0. To apply the hotfix for the Microsoft .NET Framework 3.5.1, you must have the .NET Framework 3.5.1 installed on a computer that is running one of the following operating systems:
  • Windows 7 Service Pack 1 (SP1)
  • Windows Server 2008 R2 Service Pack 1 (SP1)
Note The .NET Framework 3.5.1 feature contains the Microsoft .NET Framework 2.0 Service Pack 2 (SP2).

Restart requirement

You do not have to restart the computer after you apply this hotfix.

Hotfix replacement information

This hotfix does not replace a previously released hotfix.

File information

The global version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

File information for the .Net Framework 3.5.1

For all supported x86-based versions of Windows 7
Collapse this tableExpand this table
File nameFile versionFile sizeDateTimePlatform
System.data.dll2.0.50727.56972,927,61630-Sep-201122:34x86
For all supported x64-based versions of Windows 7 and of Windows Server 2008 R2
Collapse this tableExpand this table
File nameFile versionFile sizeDateTimePlatform
System.data.dll2.0.50727.56973,095,55230-Sep-201122:28x64
For all supported IA-64–based versions of Windows Server 2008 R2
Collapse this tableExpand this table
File nameFile versionFile sizeDateTimePlatform
System.data.dll2.0.50727.56973,234,81630-Sep-201122:21IA-64

File information for the .Net Framework 4.0

For all supported x86-based versions of Windows 7
Collapse this tableExpand this table
File nameFile versionFile sizeDateTimePlatform
System.data.dll4.0.30319.5453,027,72816-Dec-201107:59x86
For all supported x64-based versions of Windows 7 and of Windows Server 2008 R2
Collapse this tableExpand this table
File nameFile versionFile sizeDateTimePlatform
System.data.dll4.0.30319.5453,174,16016-Dec-201108:37x64
For all supported IA-64–based versions of Windows Server 2008 R2
Collapse this tableExpand this table
File nameFile versionFile sizeDateTimePlatform
System.data.dll4.0.30319.5453,366,16016-Dec-201109:16IA-64
Back to the top

WORKAROUND

To work around this issue, use one of the following methods:
  • Set the time-out connection string to the 150 value that sets a time of 150 seconds. When you do this, the data provider waits for 12 seconds to finish the first read call. (The issue does not occur if the call is finished within 12 seconds.)
  • Add the Min Pool Size=20 setting to the connection string. After you add this setting, the data provider maintains at least 20 connections in the data connection pool of the application. Therefore, more opportunities are provided to reuse an existing connection instead of opening a new connection. And when an existing connection is used, you do not receive the error messages that are mentioned in the "Symptoms" section.

    Note The default value of Min Pool Size is 0.
Back to the top

MORE INFORMATION

The following screen shot displays a network capture (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=983b941d-06cb-4658-b7f6-3088333d062f&displaylang=en)  between the App Server and SQL Server at the time that the problem occurred in a test scenario:

Collapse this imageExpand this image


In this example, we can see that the App Server closes the connection (Frame 670 AF packet) about one second into the initial connection phase (TDS:Prelogin). When the SQL Server responds at five seconds, this response is too late, and client resets the connection. As explained in the "Cause" section, when Connection Timeout is set to 15 seconds (the default value), the actual Connection Timeout that is calculated by the Connection retry algorithm for mirroring is equal to 1.2 sec (.08*15).

The following is a BID trace (http://msdn.microsoft.com/en-us/library/cc765421(SQL.100).aspx)  that was captured from the test App Server:

<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 notice that the login process timed out in a short timeframe of milliseconds.

To obtain the ring buffer output from Management Studio, run the following query:

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

Back to the top

REFERENCES

For more information about the SqlConnection.ConnectionTimeout property, visit the following MSDN website:
General information about the SqlConnection.ConnectionTimeout property (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx)
For more information about the connection retry algorithm for TCP/IP connections, visit the following MSDN website:
General information about the connection retry algorithm for TCP/IP connections (http://msdn.microsoft.com/en-us/library/ms365783.aspx)
For more information about the System.Data.SqlClient namespace, visit the following MSDN website:
General information about the System.Data.SqlClient namespace (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.aspx)
For more information about database mirroring, visit the following MSDN website:
General information about database mirroring (http://msdn.microsoft.com/en-us/library/ms189852.aspx)

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Back to the top
APPLIES TO
  • Microsoft .NET Framework 3.5 Service Pack 1
  • Microsoft .NET Framework 4
  • 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
Back to the top
Keywords: 
kbqfe kbhotfixserver kbfix kbexpertiseadvanced kbsurveynew KB2605597
Back to the top