SQL SERVER – Timeout expired. The timeout period elapsed prior to completion of the operation

Some errors when encountered take most of us for a spin. In this category the error related to “Timeout” surely falls. If you are a web developer and receive the same there are a hundred combinations why this can possibly happen. The web, results can sometimes lead us in completely opposite directions because we have not analyzed the root cause for this. I sincerely urge everyone when working with generic errors, look for specific symptoms and then use the trick of eliminating one after the other before a final solution can be arrived.

This is one of the most common error which you would hear from web developers. Here is a typical error raised shown on the website.

SQL SERVER - Timeout expired. The timeout period elapsed prior to completion of the operation timeout-01

[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

It’s very clear message that the timeout duration specified for the operation is completed but actual work didn’t finish. The error can come because of two kind of timeouts:

  1. Query Timeout: The default value for web application or .NET application is generally 30 seconds. If any request is in progress and it couldn’t complete within the timeout period, we would again see an error.
  1. Connection Timeout: The default value of connection timeout is generally 15 seconds. Within this time if the connection can’t be made, we would see the error.

The next obvious question is – what should we do if we see such error?

Solarwinds

Query timeout: The reasons a command/query runs longer than expected is commonly due to blocking or the need for query/index tuning or both.  A quick way to check for blocking to to run sp_who2 while the query is running.  The BlkBy column will show the SPID of the blocking connection if the query is blocked.  For slow running query, you may want to check the execution plan to verify that the statement is touching the rows which are needed.  For example, if your intent is to Select a single row but you see a scan operator, that is a strong indication that you need to perform index or query tuning or need to update statistics.

Here is the over-simplified repro of query timeout done via SQL Server Management Studio (SSMS). The default value of query timeout in SSMS in 0 (which is infinite) Tools > Options in menu would open below window where the value can be set, if needed.

SQL SERVER - Timeout expired. The timeout period elapsed prior to completion of the operation timeout-02

For demo purpose, I have changed it to 10 seconds. In one query window we would run below command

USE tempdb
GO
CREATE TABLE foo (i INT)
BEGIN TRAN
INSERT INTO foo VALUES (1)
GO

In second query windows, we can run below command

SELECT * FROM tempdb..foo

and we can see below after 10 seconds.

SQL SERVER - Timeout expired. The timeout period elapsed prior to completion of the operation timeout-02-1

It is important to note the error number. It is not an SQL Server error but error by client. The query waited for 10 seconds because we caused blocking. If we don’t set timeout value in SSMS, they query would run forever because default timeout is zero.

Connection timeout: This I can’t reproduce by management studio query easily. On a slow network, you can easily reproduce it by reducing the connection timeout to a lower value. In SSMS. You need to click on Options on login page as shown below:

SQL SERVER - Timeout expired. The timeout period elapsed prior to completion of the operation timeout-03

And change the value in the Connection Properties tab.

SQL SERVER - Timeout expired. The timeout period elapsed prior to completion of the operation timeout-04

On the same screen we can also set the query timeout (shown as execution time-out)

If you are seeing the same errors in your web application, then you need to check a configuration file (normally known as web. config file). The same parameters (query and connection timeout) can be set in the connection string of the application as well.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Why is My Query Switching to Row Processing and Not Doing Batch Processing?
Next Post
SQL SERVER – Add failover cluster node fails with “number of cluster nodes supported for edition”

Related Posts

35 Comments. Leave new

  • Hello Sir,
    I got Following error every day while i move database from RAM Disk to Hard Disk Because but i got Following Error Every day

    XTitle :DALHelper.GetDataTableFromQuery, XMessage : SELECT I.NAME AS INDEXNAME FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), NULL, NULL, NULL, NULL) PS INNER JOIN SYS.INDEXES I ON I.OBJECT_ID = PS.OBJECT_ID AND I.INDEX_ID = PS.INDEX_ID WHERE AVG_FRAGMENTATION_IN_PERCENT > 0 AND PS.INDEX_ID > 0 AND I.TYPE = 2 AND I.OBJECT_ID = OBJECT_ID(‘TestDATAAnalog17_9_4_12_TO_17_9_4_15’) ORDER BY AVG_FRAGMENTATION_IN_PERCENT DESC:data source=***SQLEXPRESS;initial catalog=TestHistorianData17090404;persist security info=True;user id=sa;password=***;MultipleActiveResultSets=True;,System.Data.SqlClient.SqlException (0x80131904): Cannot continue the execution because the session is in the kill state.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlDataReader.TryCloseInternal(Boolean closeReader)
    at System.Data.SqlClient.SqlDataReader.Close()
    at System.Data.Common.DbDataReader.Dispose(Boolean disposing)
    at System.Data.Common.DbDataReader.Dispose()
    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
    at XForceHistorian.DAL.Helper.DALHelper.GetDataTableFromQuery(String query, String connectionString) in F:Kalpesh X-Force Historian 1.0XForceHistorian.DALHelperDALHelper.cs:line 173
    ClientConnectionId:c506af27-4f8d-4a03-a9a3-42034948cfe3

    Reply
  • One of my windows login is trying to connect to the SQL server 2008R2 Express edition he is facing This issue as Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This Issue appears only once a day i.e. when the initial request is made to connect to the server.
    I have tried with my SA login but I didn’t find any issue for the first time login in a day.

    He has tried connecting to SQL Server with server name as well as IP address but that hasn’t solved the issue.

    Can you please suggest for this issue.

    Reply
  • Oh, THANK YOU SO MUCH!!!

    I was getting this error and I’ve changed the connection timeout to 1000 and got the same error… I was two steps away from going crazy!! xD

    I changed my SQLCommand.CommandTimeout and everything’s going fine now!!

    :D

    Reply
  • Just for clarity.

    Who raises the exception, Sql Server or C#?

    If sql server is who raises the exception, the work currently done is rolledback. i supose.
    But, if it is c# who raises the exception, sql server still runs the execution of the command?

    Reply
  • Michael Jenc
    May 7, 2020 8:12 pm

    Hi,I tried to execute a CREATE DATABASE command on a ADO command object with CommandTimeout set to 0. This worked fine in previous days. Now we encounter a timeout error after 120 seconds.
    Do you know of a change to MSOLEDBSQL or SQLNCLI11 (tried both providers) that allows only a maximum timeout? Setting it to MAXINT did not help either. Setting a value of 36000 did help though.
    But I am just interested what caused the error…

    Reply

Leave a Reply

Menu