Today we are going to learn about how to fix the error: Timeout expired. The timeout period elapsed prior to the 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 of 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 arrive.
This is one of the most common errors which you would hear from web developers. Here is a typical error raised shown on the website.
It’s a 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 kinds of timeouts:
- Query Timeout: The default value for a 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.
- 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?
Query timeout: The reason 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 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 a 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 the menu would open below the window where the value can be set if needed.
and we can see below after 10 seconds.
It is important to note the error number. It is not an SQL Server error but an error by the client. The query waited for 10 seconds because we caused blocking. If we don’t set the timeout value in SSMS, the query would run forever because the 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 the login page as shown below for the timeout period.
And change the value in the Connection Properties tab.
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 the 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)
36 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
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.
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
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?
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…
Thank you so much, this was driving me nuts!!