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
Thanks I used to get this error quite a few times. But never knew how to check the timeout settings . this helps
Good intr. I would like see peole who do their the way do. What unclear how we “Unexpected Termination” and it occurs randomly. I increasing Timeout and to 20 times but that didn’t easily. I tutorial that suggests I the stored procedure catalog.start_execution I that overkill or not? Do you have any better suggestions?
you need to figure out why more time is taken by the query randomly. Increasing timeout might be a quick fix.
After use this Step i Show this Error Again and Again
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Kashan – can you explain little more?
Hi Pinal,
1. Will changing the Connection Time out to ‘0’ in Management Studio will resolve the issue for slow connections?
2. Recently am getting this Connection timeout error, is this because too many open connections? Is the any maintenance quest for a DB admin to kill the open connections by EOD?
Thanks for this article Sir!!
if you are modyfing a table via the designer, then set the designer timeout from the settings
Hi sir I am getting same error
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
what should id do whether to increase the Query timeout and Connection timeout
when execute any procedure on database The wait operation time out occure. how to fix it?
Hi ,
I am getting error “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.:type:System.Exception” in many of my deployments. The issue comes and randomly and get fixed on its own.
I am using C# 3.5 to connect to SQL 2012. I have checked whether there are any open connections or suspended queries on the server but they are OK.
I am not sure whether issue is because of command time out or connection time out. When I run the command on the SQL studio the results are coming in 1 to seconds.
With the help of It team I have checked the connectivity between application and database server and they said that it is OK.
Can some body guide me to address the issue.
Thanks in Advance,
Vijay
Can you please update the statistics…by running exec spUpdate_Statistics
System.Data.EntityCommandExecutionException was caught
HResult=-2146232004
Message=An error occurred while executing the command definition. See the inner exception for details.
Source=System.Data.Entity
StackTrace:
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, ReadOnlyMetadataCollection`1 entitySets, EdmType[] edmTypes, MergeOption mergeOption)
at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, MergeOption mergeOption, ObjectParameter[] parameters)
at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ObjectParameter[] parameters)
at LabelService.DAC.DB.LabelServiceEntities.InsertLocalisation(Nullable`1 createdAt, Nullable`1 createdBy, Nullable`1 updatedAt, Nullable`1 updatedBy, Nullable`1 labelID, String value, Nullable`1 parameterTupelID, Nullable`1 historyIndex, Nullable`1 translated, Nullable`1 translatedBy, Nullable`1 translatedAt, Nullable`1 released, Nullable`1 releasedBy, Nullable`1 releasedAt) in c:\WebComponent_New\LabelService\Projects\DAC.DB\LabelService.Context.cs:line 512
at LabelService.DAC.DB.LocalisationAccessor.InsertLocalisation(Localisation p_Localisation, User p_Editor) in c:\WebComponent_New\LabelService\Projects\DAC.DB\LocalisationAccessor.cs:line 342
InnerException: System.Data.SqlClient.SqlException
HResult=-2146232060
Message=Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=11
LineNumber=0
Number=-2
Procedure=””
Server=tcp:wispdb02.ww004.siemens.net,1433
State=0
StackTrace:
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.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
InnerException: System.ComponentModel.Win32Exception
HResult=-2147467259
Message=The wait operation timed out
ErrorCode=-2147467259
NativeErrorCode=258
InnerException:
Sir, I am getting above error but this is always not generate. When users increase then got this error. Please can you suggest me the solution.
Thanks In Advance.
you need to figure out what is happening in SQL Server. I generally use profiler.
Hi Dave,
I need to fetch data from remote server having more than 90 million records within every table of remote database. After fetching data in temporary table on DestinationDB I used INSERT or UPDATE, so that I can Insert/Update my DestinationDB. So I’m using below query:
select
[doc_typing].[doc_type_id],
[doc_typing].[description]
into #doc_typing from [ttt01\report].[db_report].[dbo].[doc_typing] with (nolock);
update [doc_typing] set
[doc_typing].[doc_type_id]=b.[doc_type_id]
, [doc_typing].[description]=b.[description]
from [doc_typing], #doc_typing as b where [doc_typing].[doc_type_id]=b.[doc_type_id]
select @rowsupdated =@@rowcount;
insert into [doc_typing](
[doc_type_id]
, [doc_typing].[description]
) select * from #doc_typing where doc_type_id not in (select doc_type_id from doc_typing) ;
;
;
select @rowsinserted=@@rowcount;
But I faced issue “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”
I have already put TimeOut to 5 minutes.
You may need to tune your query. You may want to look at SSIS package for such huge data related task.
can any body help the issue we are facing : The timeout period elapsed prior to completion of the operation or the server is not responding.
I do have the default setting as 0, meaning unlimited wait and no time-out. I still receive this error message, my case is SQL2008R2, adding a new ID as primary key to a table with 73M records. Thank you.
you got any solution?? im facing the same eror, cant figure out why? pls help if you already solved your isssue
You need to figure out what is happening in SQL Server. I generally use profiler.
I am getting connection timed out message when trying to connect to the SQL instance using SSMS. Our IPs got changed to a new series and this is when the problem started. After increasing the connection time out seconds to 20, I was able to connect to few instances, but there are two instances now that wont connect even after changing the time out to 20 seconds. please advise.
looks like DNS issue.
Thank you
Welcome vaibhavi.
i am getting this error when scheduled report executed. Could you please help me to resolve this error.
Exception occurred while rendering the report : System.Web.Services.Protocols.SoapException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source ‘DataSource’. —> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at Microsoft.ReportingServices.Library.ReportExecution2005Impl.LoadReport(String Report, String HistoryID, ExecutionInfo2& executionInfo)
at Microsoft.ReportingServices.WebServer.ReportExecutionService.LoadReport(String Report, String HistoryID, ExecutionInfo& executionInfo)
are you able to connect to data source outside report? I am not sure how much I can help via blog comments.
I am getting the same error on application. This is my client application, I have migrated the server and on new server I am getting this error. As I am checking the Event logs and Warning on ASP2.0 ” Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.” Please for solution. I have tried on SQL side like connection timeout increase value.
have you changed connection string after migration? You need to do basic connectivity test first.
All I had to do was create an Index.
In Sql Server Management Studio, highlight the query and click Display Estimated Execution Plan.
Index Seek is good. If there is an index scan, create an index on the column(s) on your table.
This fixed the problem for me.
In my case network administrator has enabled firewall on server which was queried in openquery…