Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Vijay Kumar 2,036

Hi Team,

We are getting below error while running query in SSMS:
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Normally it shouldm't take more than 2 mins. But from yesterday onwards, the query failing with above error.
It is SQL Server 2016 With SP2 (CU17)

Accepted answer

Erland Sommarskog 112.7K Reputation points MVP

2021-07-07T21:51:14.297+00:00

By default, SSMS waits forever for a query to complete. If you get this error, this is because you have set a query timeout under Tools->Options->Query Execution->SQL Server->General->Execution time-out. You can also change it for the current window, by right-clicking and selecting Query Options from the bottom of the context menu. Set the time-out to 0 to wait forever.

As for why the query is running slower than yesterday... Well, have you investigated if it may be due to blocking? Else it is not unheard of that the execution plan changes, because of updated statistics or some other reason. And sometimes when the optimizer takes a wrong bet, the query can be a lot slower.

Do you have Query Store enabled on the database?
Please sign in to rate this answer.

1 person found this answer helpful.
Kiki Newberry 0 Reputation points

2023-02-09T18:45:13.0633333+00:00

I just encountered the same issue. Could you please guide me how to get to the Tools? Is it in Power BI or SQL Server?

Thank you for your help!

Erland Sommarskog 112.7K Reputation points MVP

2023-02-09T22:07:29.86+00:00

This thread is about SSMS, and you have question about PowerBI? I suggest that you ask a new Question, describing your problem from start to end, rather than piggybacking on old threads.

Kennedy Lekoko 0 Reputation points

2023-07-19T08:53:13.2766667+00:00

Hi Erland, I have followed this but still when my endpoint API call happens, I still get a timeout. Is there something else I could do? When I execute the SP on SSMS it takes 45s to complete but from the API it timesout.

Erland Sommarskog 112.7K Reputation points MVP

2023-07-19T09:29:04.1233333+00:00

Yes, there is something else you can do. To wit, you should follow the house rules and don't piggyback on old threads, but ask a new Question, describing your problem from start to end.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

2 additional answers

AmeliaGu-MSFT 13,986 Reputation points Microsoft Vendor

2021-07-08T03:05:55.173+00:00
Hi VijayKumar768,

In addition, please try to clear the statistics to make sure the database's statistics and/or query plan cache are correct:

exec sp_updatestats

And please try to track the query using SQL Server Profiler to see if can get some clues.

Best Regards,
Amelia
Please sign in to rate this answer.

2 people found this answer helpful.
Anonymous

2022-12-25T09:12:14.583+00:00

Hi AmeliaGu-msft
where I can find exec sp_updatestats

Erland Sommarskog 112.7K Reputation points MVP

2022-12-25T10:51:16.28+00:00

I don't think Amelia is around here any more. I have not seen her for a while.

Anyway, sp_updatestats comes with SQL Server, so you don't have to search for it.

If that does not answer your question, please post a new question and describe your problem from start to end, including which environment you are working in.

Rodney Rizo 0 Reputation points

2023-02-15T01:57:16.71+00:00

It worked for me, you're amazing @AmeliaGu-MSFT <3
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Andrew 6 Reputation points

2021-08-05T08:36:07.917+00:00

I had the same problem - after regularly running large queries, suddenly getting a timeout on some - but not all? - queries.

I tried Tools->Options->Query Execution->SQL Server->General->Execution time-out - changed the value there from 15secs to 600secs - but I had to update the setting in Query>Query Options to fix it properly.

Was it a SSMS update that changed the settings? It's been working fine up until last Thursday (29 July)
Please sign in to rate this answer.

1 person found this answer helpful.
Erland Sommarskog 112.7K Reputation points MVP

2021-08-05T21:22:21.767+00:00

15 seconds is a non-default value. Apparently someone had set it to 15 seconds. The normal value is 0 (wait forever).

The setting is a little difficult to change, as you may have noticed. First of all, it does not affect the current window; only new windows opened after the change, but you can change it for an existing window from Query Options as you have noticed. then again, this is true for many settings in SSMS.

But you may find that next time you start SSMS, it is back on 15 seconds! What I found is that if I want to change the setting, and want the change to stick, I need to

Start SSMS.

Go into Tools->Options can change the setting.

Open a new query window.

Close the window that was opened when SSMS started.

Close SSMS.

Andrew 6 Reputation points

2021-08-23T08:59:39.413+00:00

II thought it had come right but no...

What is very strange is:

It is only happening on certain servers that I connect to (defatul executrion timeout of 15seconds) but not all - the rest are all at 0 seconds.

The settings for the timeout are different between:
Tools>Options>Query Execution>Execution Time-out (referenced above) --- this value is 0.
Query>Query Options>General>Execution Time-out --- this value is 15.

I've tried udating the value, then closing everyhting down without running any queries, but every time I start SSMS, for these 2 particular servers, the values go back to these defaults above.

II don't have rights on the servers to run sp_updatestats.

Andrew 6 Reputation points

2021-08-23T09:11:34.013+00:00

Some further digging uncovered the answer for me.

Thanks to :
https://blog.sqlauthority.com/2016/01/26/sql-server-timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation-or-the-server-is-not-responding/

It's a value set in the server connection settings.

Connect>Database Engine>Options>>>Execution time-out

For both of the servers with which I have had problems, there was a value of 15 seconds stored here.

Why there are two places to set a value for this, and why the two values can be different, I don't know. But at least it's working now for me.

If this value is set within the client rather than on the server then I am very confused as to how this setting of 15 seconds orignated; it certainly wasn't a setting that I updated. Perhaps one of our IT support staff made the change as part of some testing.

Erland Sommarskog 112.7K Reputation points MVP

2021-08-23T20:37:09.993+00:00

Thanks for the update!

The fact that there are two settings may explain the issues that I described in my previous post. I think I may have overlooked that you can set this for the server as well.

And, yes, these values have been set at the client. There is no notion about execution timeouts in SQL Server.

I would guess that if you had typed the server names rather than using the dropdown, you would not have picked up that default of 15 seconds.

omar theibich 1 Reputation point

2022-10-19T10:26:21.23+00:00

how can i solve the same problem but in server share not local ,any query can use to solve it?

Erland Sommarskog 112.7K Reputation points MVP

2022-10-19T20:55:40.233+00:00

It is never a good idea of piggy-backing on an old thread. Post a new question, describing your problem from start to end. Refer to this thread if you like.

Jia Zhuang 0 Reputation points

2024-11-06T16:40:18.92+00:00

I set the timeout to 0 to wait forever, but I still got a query execution time-out error. Then I ran exec sp_updatestats, and the error was gone. The program ran quickly.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

2 additional answers

Your answer