Win32Exception (0x80004005): The wait operation timed out

44

I'm running an ASP.NET Web Pages page that upon initial load pulls a list of items from a SQL server. This query runs in a second or so and loads the page within 2 seconds. The return is about a 1000 records, give or take. I'm pulling Hostnames from a Service Manager SQL database along with some other information.

Within this page, I have a search built in that essentially runs the exact same query but runs it with a LIKE based on hostname. This loads the same page with all hostnames that are contain part of the search query. The query generally runs within SQL Management Studio in under a second, but loading the page takes substantially longer and sometimes it times out.

My question is, why does the parameter based search takes so much longer and sometimes timeout for no apparent reason. Are there any steps that can be taken to mitigate this timeout? Below is the full error.

Server Error in '/' Application.


The wait operation timed out 

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:

System.ComponentModel.Win32Exception: The wait operation timed out
Source Error: 

Line 13:     }
Line 14:     
Line 15:     var selectedData = db.Query(selectCommand, searchTerm);
Line 16: 
Line 17:

Source File:  c:\Users\u0149920\Documents\My Web Sites\AppSupport\servers\default.cshtml    Line:  15

Stack Trace:

[Win32Exception (0x80004005): The wait operation timed out]
[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, Action`1 wrapCloseInAction) +1753346
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5295154
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +242
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1682
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +59
   System.Data.SqlClient.SqlDataReader.get_MetaData() +90
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +365
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +1325
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +175
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41
   System.Data.Common.DbCommand.ExecuteReader() +12
   WebMatrix.Data.<QueryInternal>d__0.MoveNext() +152
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +381
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
   WebMatrix.Data.Database.Query(String commandText, Object[] parameters) +103
   ASP._Page_servers_default_cshtml.Execute() in c:\Users\u0149920\Documents\My Web Sites\AppSupport\servers\default.cshtml:15
   System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +197
   System.Web.WebPages.WebPage.ExecutePageHierarchy(IEnumerable`1 executors) +69
   System.Web.WebPages.WebPage.ExecutePageHierarchy() +151
   System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage) +76
   System.Web.WebPages.WebPageHttpHandler.ProcessRequestInternal(HttpContextBase httpContext) +114

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.17929

sql-server-2008
asp.net-webpages
asked on Stack Overflow May 3, 2013 by Bolson32 • edited Nov 19, 2013 by reporter

9 Answers

32

The problem you are having is the query command is taking too long. I believe that the default timeout for a query to execute is 15 seconds. You need to set the CommandTimeout (in seconds) so that it is long enough for the command to complete its execution. The "CommandTimeout" is different than the "Connection Timeout" in your connection string and must be set for each command.

In your sql Selecting Event, use the command:

e.Command.CommandTimeout = 60

for example:

Protected Sub SqlDataSource1_Selecting(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
    e.Command.CommandTimeout = 60
End Sub
answered on Stack Overflow Dec 23, 2013 by Jeff
23

To all those who know more than me, rather than marking it unhelpful or misleading, read it one more time. I had issues with my Virtual Machine (VM) becoming unresponsive due to all resources being consumed by locked threads, so killing threads is the only option I had. I am not recommending this to anyone who are running long queries but may help to those who are stuck with unresponsive VM or something. Its up-to individuals to take the call. Yes it will kill your query but it saved my VM machine being destroyed.

Serverstack already answered similar question. It solved my issue with SQL on VM machine. Please check here

You need to run following command to fix issues with indexes.

exec sp_updatestats
answered on Stack Overflow Sep 19, 2013 by Sanjay Zalke • edited Apr 13, 2017 by Community
6

If you're using Entity Framework, you can extend the default timeout (to give a long-running query more time to complete) by doing:

myDbContext.Database.CommandTimeout = 300;

Where myDbContext is your DbContext instance, and 300 is the timeout value in seconds.

(Syntax current as of Entity Framework 6.)

answered on Stack Overflow Mar 10, 2017 by Jon Schneider
5

I had the same issue. Running exec sp_updatestats did work sometimes, but not always. I decided to use the NOLOCK statement in my queries to speed up the queries. Just add NOLOCK after your FROM clause, e.g.:

SELECT clicks.entryURL, clicks.entryTime, sessions.userID
FROM sessions, clicks WITH (NOLOCK)
WHERE sessions.sessionID = clicks.sessionID AND clicks.entryTime > DATEADD(day, -1, GETDATE())

Read the full article here.

answered on Stack Overflow Sep 2, 2015 by Flo
4

I tried the other answers here as well as a few others. I even stopped and restarted the SQL services. Nothing worked.

However, restarting my computer did work.

answered on Stack Overflow Apr 9, 2015 by MiniRagnarok
4

Look into re-indexing tables in your database.

You can first find out the fragmentation level - and if it's above 10% or so you could benefit from re-indexing. If it's very high it's likely this is creating a significant performance bottle neck.

http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/

This should be done regularly.

answered on Stack Overflow Jan 28, 2016 by niico
2

We encountered this error after an upgrade from 2008 to 2014 SQL Server where our some of our previous connection strings for local development had a Data Source=./ like this

        <add name="MyLocalDatabase" connectionString="Data Source=./;Initial Catalog=SomeCatalog;Integrated Security=SSPI;Application Name=MyApplication;"/>

Changing that from ./ to either (local) or localhost fixed the problem.

<add name="MyLocalDatabase" connectionString="Data Source=(local);Initial Catalog=SomeCatalog;Integrated Security=SSPI;Application Name=MyApplication;"/>
answered on Stack Overflow Feb 11, 2016 by Brett Bim
1

My Table didn't have primary key then I had time out error. after set key sloved.

answered on Stack Overflow Oct 24, 2016 by leyla azari
0

This issue will occur due to the sqlcommand execution time. Set the CommandTimeout = 100 or desire value

Timeout value in seconds @Note Its better to provide the optimal value

answered on Stack Overflow Apr 4, 2019 by mydeenali

User contributions licensed under CC BY-SA 3.0