Timeout Expired is a common error in SQL Server. The image below shows an error screenshot with the message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The detailed error description in the log is as follows:
[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33 System.Data.SqlClient.SqlDataReader.get_MetaData() +83 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12 System.Data.Common.DbCommand.ExecuteReader() +12 System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +332
The above output clearly describes that the timeout period for this operation has ended, yet the task was not completed.
Today, let’s take a look at how to identify the causes of this issue in different scenarios and how to resolve it.
When will you have Time Expired error in SQL Server and how to fix it?
We often encounter ‘timeout’ errors, but at which specific stage of access and influenced by which settings does this error occur? Below, we’ll analyze the timeout issue using a typical web application architecture.
From the access relationship diagram above, you can see that from the perspective of the database instance, it receives access requests from the web, direct access from users, and it may also access other database instances simultaneously. Therefore, connections are divided into incoming connections and outgoing connections. We can categorize timeout issues based on the different stages of access.
Incoming Connection Web Access Timeout
First, let’s look at the web access timeout for incoming connections: there are ASP.NET request timeouts, WebService request timeouts, IIS request timeouts, database connection timeouts, and query timeouts.
ASP.NET Request Timeout
The execution timeout for ASP.NET pages can be set in multiple places.
From MSDN’s explanation:
httpRuntime is a configuration setting for ASP.NET’s HTTP runtime, determining how requests to an ASP.NET application are handled.
executionTimeout: Specifies the maximum time allowed for request execution, measured in seconds. The default value is 90 seconds.
maxRequestLength: Indicates the maximum file upload size supported by ASP.NET. This limit can be used to prevent denial of service attacks caused by users uploading large files to the server. The size is specified in KB, with a default value of 4096 KB (4 MB).
1. Global Timeout Setting
If there are multiple websites on the server and you want to set a unified timeout value, you need to configure the ExecutionTimeout attribute in the Machine.config file. The Machine.config file is located in the %SystemRoot%\Microsoft.NET\Framework\%VersionNumber%\CONFIG\ directory.
For example:
<httpRuntime executionTimeout="90" maxRequestLength="4096" useFullyQualifiedRedirectUrl="false" minFreeThreads="8" minLocalRequestFreeThreads="4" appRequestQueueLimit="100" />
2. Timeout for a Single Site
Set the HTTP request execution time in the Web.config configuration file:
<system.web> <httpRuntime maxRequestLength="102400" executionTimeout="720" /> </system.web>
The timeout here is set to 720 seconds. The preceding attribute, maxRequestLength, is typically used to limit the size of files uploaded by users, with a default value of 4096 KB (4 MB).
3. Timeout for a Single Page Request
For a single page, you can use Server.ScriptTimeout to set the timeout.
Server.ScriptTimeout = 120;
Note: If the debug attribute is set in the Web.config file, for example:
<compilation debug="true" targetFramework="4.0">
In this case, ScriptTimeout will be ignored.
WebService Request Timeout
Increase the timeout limit for the proxy class, which is set to 90 seconds by default, by specifying the timeout period before invoking the method.
YourWebService yws = new YourWebService(); yws.Timeout = 1200000; //20 minutes, measured in milliseconds
If the Timeout property is set to Timeout.Infinite, it indicates that the request has no timeout. However, even if the XML Web Services client sets the Timeout property to no timeout, the web server can still time out the request on the server side.
IIS Request Timeout
In IIS Manager, select Sites, click Website Defaults on the right side, and set the connection timeout in the Limits property list under Connection Time-out (seconds). The default value is 120 seconds.
Connection timeouts help reduce the loss of processing resources consumed by idle connections. When connection timeouts are enabled, IIS enforces the following types of connection timeouts at the connection level:
-
Connection timeout due to the client having sent data to the server and then remaining idle.
-
Server listen timeout when a connection is established with the server, but the client does not send any data.
-
Response timeout (based on a configurable minimum bytes-per-second value).
-
Request timeout, which prevents clients from sending unreasonably slow requests to the server (e.g., 1 bit per second).
Database Connection Timeout
In the .NET SqlConnection class, there is a ConnectionTimeout property that specifies the time (in seconds) to wait before terminating an attempt and generating an error when trying to establish a connection. The default value is 15 seconds. If the connection is not established within this time, the error will occur. A value of 0 indicates no limit.
It is primarily controlled through the Connect Timeout in the connection string, as follows:
<connectionStrings> <add name="conn" connectionString="user id=crm;Password=crmpwd;initial catalog=DBName;Server=DBServerFQDN;Connect Timeout=30;" providerName="System.Data.SqlClient" /> </connectionStrings>
Database Query Timeout
In the .NET SqlCommand class, there is a CommandTimeout property that specifies the time (in seconds) to wait before terminating an attempt to execute a command and generating an error. The default value is 30 seconds. If the request is running and does not complete within this timeout period, the error will occur. A value of 0 indicates no limit.
It is primarily controlled through SqlCommand.CommandTimeout, as follows:
SqlCommand command = new SqlCommand(queryString, connection); // Setting command timeout to 1 second command.CommandTimeout = 1;
Another type of timeout to consider is in the .NET SqlBulkCopy class, which has a BulkCopyTimeout property that specifies the number of seconds allowed before the operation times out. If the operation times out, the transaction will not be committed, and all rows that have been copied will be removed from the target table. The default timeout setting for bulk loading data using SqlBulkCopy is 30 seconds.
When dealing with database connections, you might encounter connection timeouts or command timeouts, which are different from each other. Using ADO.NET as an example, the main timeout scenarios encountered when connecting between a client and a server include:
-
Timeout when acquiring a connection from the connection pool.
-
Timeout when establishing a new connection (rather than getting one from the connection pool).
-
Timeout when sending a command to SQL Server.
-
Timeout when sending a command (with the connection string containing the “context connection=true” property) to SQL Server.
-
Timeout when implicitly sending a command to SQL Server.
-
Timeout when executing an asynchronous command (
BeginExecute). -
Timeout when retrieving rows from the server.
-
Timeout when uploading data using Bulk Copy.
These timeouts are primarily controlled through Connect Timeout in the connection string and SqlCommand.CommandTimeout. The first two types are login timeouts determined by Connection Timeout, while the latter types are command timeouts determined by Command Timeout.
Note: Errors such as “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding” generally indicate that SqlCommand.CommandTimeout or SqlBulkCopy.BulkCopyTimeout has expired, rather than SqlConnection.ConnectionTimeout.
Incoming Connection SSMS Access Timeout
Next, for incoming connections, let’s look at the timeout settings when users access the database via SQL Server Management Studio (SSMS).
We can set both connection and query timeouts in the SSMS tool. The default value for connection timeout is 15 seconds, while the default value for query timeout is 0, which means the query will run indefinitely until it completes.
Outgoing Connection Cross-Instance Remote Access Timeout
Finally, for outgoing connections, let’s examine the timeout settings for cross-instance remote database access.
To access a database from one instance to another, you can view the settings as follows:
Check the configuration options settings:
sp_configure
Remote Login Timeout
The remote login timeout option specifies the number of seconds to wait before returning a failure when logging into a remote server. For example, if you attempt to log in to a remote server that is down, the remote login timeout helps you avoid waiting indefinitely before your machine stops trying to log in. The default value for this option is 10 seconds. A value of 0 indicates an infinite wait.
In SQL Server 2008, the default value for this option is 20 seconds.
The remote login timeout option affects connections made by OLE DB providers in heterogeneous queries.
This setting takes effect immediately without requiring a service restart.
In SQL Server 2014, the default remote login timeout is 10 seconds. The following script can be used to modify this value:
EXEC sp_configure 'remote login timeout', 35 ; GO RECONFIGURE ; GO
Remote Query Timeout
The remote query timeout option specifies the number of seconds a remote operation should take before SQL Server times out. The default value is 600 seconds, allowing for a 10-minute wait. This value applies to outgoing connections initiated by the database engine as remote queries. This value does not apply to queries received by the database engine. To disable the timeout, set it to 0, which means the query will wait indefinitely until canceled.
For heterogeneous queries, the remote query timeout specifies the number of seconds a remote provider should wait for results before the query times out (using the DBPROP_COMMANDTIMEOUT rowset property in the command object initialization). If supported by the remote provider, this value can also be set by the user with DBPROP_COMMANDTIMEOUT. After the specified number of seconds, this will cause any additional operations to time out.
For remote stored procedures, the remote query timeout specifies the number of seconds to wait before timing out after sending a remote EXEC statement.
This setting takes effect immediately without requiring a service restart.
In SQL Server 2014, the default remote query timeout is 10 minutes, and you can modify this value using the following script:
EXEC sp_configure 'remote query timeout', 0 ; GO RECONFIGURE ; GO
Options for Remote Servers and Linked Servers
When configuring remote access, you can set timeout options for linked servers, which include connection timeout and query timeout.
The syntax for setting these options is as follows:
sp_serveroption [@server = ] 'server' ,[@optname = ] 'option_name' ,[@optvalue = ] 'option_value' ;
