Win32Exception (0x80004005): The wait operation timed out

Question

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

Seraphic

Answer 1

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

Manganin

I just solved this problem on a web app where the query was indeed taking too long - because nobody had created an index on the DB for the field that was being queried! This should have been the first port of call :) – Electroencephalograph

Answer 2

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

Quarterage

This isn't a generally applicable answer, i.e. queries can run longer than any given timeout regardless of whether index statistics have been updated recently. – Gabriello

Be sure to read and understand the consequences of exec sp_updatestats before running. – Boyles

Yes I am facing it in the VM. – Dieppe

This isn't a bad suggestion but is overkill and requires context. If the CommandTimeout is being hit due to a slow query, this might fix it because it helps sql server find a better execution plan. However, this command updates ALL stats but the problem is likely that ONE table received enough updates to skew a plan (ie the plan assumes 1 row with a matching value but the reality is 100). You're best off finding the slow query with profiler, getting it's execution plan, and looking for a large difference between actual and estimated row counts to find the ONE table that needs stat updates. – Beadle

@b_levitt, please understand the scenario, I was trying to save my VM at that time. VM was unresponsive so saving the work done was way important that thinking about the query performance. Once I had recovered the VM I tunned the DB with all that I could do. – Quarterage

yup, totally get it and i didn't downvote. I'm just saying when you use a sledgehammer in a pinch, you just need to point out that it IS a sledgehammer :). – Beadle

Answer 3

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.)

Residency

Thanks, it helped me. Does it mean my queries are badly structured, too heavy or something? I checked resulting sql transactions in SQL Server Profiler and found nothing strange. – Electrophilic

Answer 4

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.

J

NOLOCK is a great solution when your server is doing a lot of multi-user work or a lot of long-running report stuff! – Industrial

NOLOCK is usually a bad solution to most problems. (blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere): 1) "Dirty read" 2) Missing rows 3) Reading rows twice 4) Reading multiple versions of the same row 5) Index corruption 6) Read error and more. Just google 'NOLOCK bad idea'... (P.S. Snapshot Isolation could be your friend in some of the cases) – Maurene

I disagree that using NOLOCK is "usually a bad solution to most problems." It really depends on the issue at hand and that you understand what NOLOCK is doing. There are some scenarios where the overall perfection of the data isn't very important. There are also scenarios where it is - in which case, you should minimize query time/time spent with locked tables. – Rusch

Not use NOLOCK ? – Ladew

Answer 5

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.

Circumscription

This works for a time, but the issue comes back, and requires another restart, is there an actual permanent fix? – Valenba

Answer 6

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.

Sepulveda

tl;nr exec sp_MSforeachtable 'DBCC DBREINDEX(''?'','' '',90)' – Christoforo

Answer 7

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;"/>

Retinitis

I had the same problem after upgrading from SQL Server 2012 to 2014. The SQL Server and Web server were on different machines but changing the Data Source from SERVER to SERVER.domain.local seems to have fixed it. – Spiegleman

Answer 8

I had the same issue, and by Running "exec sp_updatestats" the issue solved and works now

Bonnard

Answer 9

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

Muro

Answer 10

EfCore version of @JonSchneider's answer

myDbContext.Database.SetCommandTimeout(999);

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

(Syntax current as of Entity Framework Core 3.1)

Radioscopy

Answer 11

In our case we were able to narrow the cause down to a number of views which had WITH SCHEMABINDING on them. Although this is supposed to improve performance it was resulting in an awful query plan (doing a single record update on a table which was being referenced by these views was taking nearly 2 seconds of elapsed time). Removing WITH SCHEMABINDING has meant all is running smoothly again and the "wait operation timed out" errors have gone.

Purpleness