Article ID: 2555235 - View products that this article applies to.
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.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
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
(http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx)property (15 seconds), you will receive this error message at 1.2 seconds, when running into the issue that is described in this article.
As per the Connection Retry Algorithm for Mirroring
(http://msdn.microsoft.com/en-us/library/ms365783.aspx), 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.
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 of the problem requires you to diagnose and resolve the underlying problems that are contributing to the slowness.
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:
The following screenshot shows a network capture
(http://www.microsoft.com/downloads/en/details.aspx?FamilyID=983b941d-06cb-4658-b7f6-3088333d062f&displaylang=en)between the application server and SQL Server at the time the problem was happening 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) 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
(http://msdn.microsoft.com/en-us/library/cc765421(SQL.100).aspx)that was captured from the test App server shows the following:
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:
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.
Article ID: 2555235 - Last Review: December 19, 2011 - Revision: 4.0