Issue: 

1 . “Timeout expired” error when you try to connect to SQL Server 2012 through an AlwaysOn availability group listener.

2. Application “Timeout” Errors in SQL Server

timeout error

Causes:

Note: The time-out error typically occurs within several seconds of the connection attempt from the application. Frequently, the time within which the error occurs is less than 5 seconds.

If you increase the Connection Timeoutproperty from the default value of 15 seconds to 20 or 30 seconds, the error still occurs. However, if you increase theConnection Timeout property to a much larger value such as 150 or 200 seconds, the connection usually succeeds.

****************************************************************

 

Example:  ” Timeout”  Issue on AlwaysON

When you try to connect to Microsoft SQL Server 2012 through an AlwaysOn availability group listener from a client application that uses the Microsoft .NET Framework Data Provider for SQL Server (System.Data.SqlClient), you may receive the following time-out error message:

“Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”

Reasons:

You receive this message when the following conditions are true:

  • Your availability group listener is in a single or multiple subnet.
  • The application uses the .NET Framework Data Provider for SQL Server that is installed together with one of the following:
    • The Microsoft .NET Framework 3.5 Service Pack 1 (SP1)
    • The Microsoft .NET Framework 4
    • The Microsoft .NET Framework 4.5
  • You specify the parameter multisubnetfailover=yes or multisubnetfailover=true in the SQL Server connection string.
  • Note: The time-out error typically occurs within several seconds of the connection attempt from the application. Frequently, the time within which the error occurs is less than 5 seconds. If you increase the Connection Timeoutproperty from the default value of 15 seconds to 20 or 30 seconds, the error still occurs. However, if you increase theConnection Timeout property to a much larger value such as 150 or 200 seconds, the connection usually succeeds.

Cause:

The issue occurs because of an issue in the .NET Framework Data Provider for SQL Server.

When you use the parameter multisubnetfailover=yes or multisubnetfailover=true, the provider retries the TCP connections. To implement the retry algorithm, the provider divides the actual Connection Timeout value into small chunks or slices in order to speed up the number of retries. For example, the following are the waiting times when the value of Connection Timeout is set to its default of 15 seconds:

  • Waiting time (time slice) for the first connection attempt is 15 * 0.08 ~ 1.2 seconds.
  • Waiting time (time slice) for the second connection attempt is 15 * 0.16 ~ 2.4 seconds.

However, if a response is slow and the connection is not completely established before the waiting time expires, the data provider incorrectly sets the connection to a doomed state. Additionally, the provider incorrectly throws a time-out error after the expiration of the waiting time (time slice) instead of the actual or full Connection Timeout value.

Note The slow response in establishing a connection may in this case be triggered either by the server or by network latency.

*************************************************************

Timeout errors are client side and the error is coming from the provider (or client) being used with the database connection. SQL Server does not necessarily keep track or offer any intuitive method to track these down.

**************************************************************

Blocking could certainly contribute to timeouts and can be fairly easily tracked. A timeout that is not related to a block has some other issue. The 30 second timeout is a common clientsetting, but it can be controlled by the command object. If you set it to 0 then the connection will not timeout.

 

Resolution:

Work around:

Method 1 (recommended)

Set the Connection Timeout or Connect Timeout property to a large value such as 200. When you do this, the data provider sets the waiting time (time slice) for the first connection attempt to 200 * 0.08 ~ 16 seconds.
Change Connection string to increase time out:

Method 2

If you use a SQL Server 2012 AlwaysOn availability group listener in a single subnet scenario, you can either remove the parameter multisubnetfailover=true from the connection string or use the parameter multisubnetfailover=false ormultisubnetfailover=no.

MS Solutions: https://support.microsoft.com/en-in/kb/2879704

http://www.codeproject.com/Questions/459265/Timeout-expired-SQL-Execution-error

http://dba.stackexchange.com/questions/80165/tracking-down-application-timeout-errors-in-sql-server

Advertisements