Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the oper

[po]

Hi ,

We have an azure sql database and using virtual neywork data gateway to refresh reports via the service and these work o.k.

However, one report seeing an issue with is incremnetal refresh one which looks to be consitently timing out after 80 minutes.

Is ther some setting which is controlling this - not set at power bi desktop level and in ssms doesn't appear to be set.

How can se wee where it is set and increase if necessary?

Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-

Thanks

[Anonymous]

Hi, @po 

By default, SSMS waits forever for the query to complete. If you get this error, it's because a query timeout is set under Tools -> Options->Query Execution->SQL Server->General->Execution Timeouts. 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 timeout to 0 to wait permanently.

In general, long-running queries are the root cause of timeouts. Try to optimize your queries to reduce execution time. You can use SQL Server's execution planning and performance tuning tools to identify bottlenecks. Make sure that Azure SQL Database has enough resources. You may need to scale up your database to a higher service tier to handle your workload more efficiently.
You can also try using Azure SQL Database's monitoring tools to track query performance and identify any issues. You can use extended events or SQL traces to identify queries that are causing timeouts. You can check the following link:

Troubleshoot query time-out errors - SQL Server | Microsoft Learn

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[po]

Thanks for replies.

It is an azure sql database - on the clinet ssms it is 0 so runs permanently.

However, in power bi an incrementallly refreshed report timing out after 80 minutes.

Other reports we see tiemout afetr 5 hours in premium workspace thus don't understnad wheer ethe 80 minutes comes from - report itself has no timeout set so woudl have thought woudl eithre run for 10 minutes and timeout or 5 hours and timeout.

Given other reports and sqls can run over 10 minutes seems not to be a database timeout and just wondering if some setting somwhere for incremental refresh gest set to 80 minutes?

Howeverm in power bi 

[lbendlin]

Did you consider my suggestion to modify the command timeout setting?

[po]

Yes - thanks for suggestion.  Not done yet as we have many reports and ideally woudl have global timeout setting.  Looking at the documenattion it suggests timeout of 10 minutes and we have other models which run for over 20 minutes successfully.

My understanidng is there is a default 30 second connection timeout and a 10 minute sql execution timeout as we have not changed any defaults and the model itself has no timeout specified in the M query thus not sure where the 80 minutes timeout coming from?

Any thoughts?

Also the report in service  attempted to run 4 times - is this the defalut for on demand reports - attempts 4 times if fails first time?

Details

# Type Start End Duration Status Error 1 Data 18/08/2024, 10:08:42 18/08/2024, 11:28:56 01:20:14.326 Failed 2 Data 18/08/2024, 11:29:56 18/08/2024, 12:50:22 01:20:25.938 Failed 3 Data 18/08/2024, 12:52:22 18/08/2024, 14:12:49 01:20:26.600 Failed 4 Data 18/08/2024, 14:17:49 18/08/2024, 15:38:16 01:20:26.603 Failed

[lbendlin]

yes, the four attempts are the new standard and we are not happy about it. It greatly messes with capacity management.

[lbendlin]

Modify the Command Timeout when you call the database

Power Query Azure SQL database connector - Power Query | Microsoft Learn