question

VijayKumar768 avatar image
1 Vote"
VijayKumar768 asked ErlandSommarskog commented

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

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)

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndrewSWright avatar image
1 Vote"
AndrewSWright answered ErlandSommarskog commented

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)

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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
1) Start SSMS.
2) Go into Tools->Options can change the setting.
3) Open a new query window.
4) Close the window that was opened when SSMS started.
5) Close SSMS.

2 Votes 2 ·

II thought it had come right but no...

What is very strange is:
1. 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.
2. 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.







0 Votes 0 ·

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.

0 Votes 0 ·
Show more comments

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

0 Votes 0 ·

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.

0 Votes 0 ·