Timeout Refresh errors azure sql sources; refresh time range 15 to 180 min? How to diagnose?

[Anonymous]

1) 2 pbix in powerbi service

2) refresh schedule at 12am and 2am when nothing is running on our azure sql instance

2a) refresh durations range from 17 minutes to 3 hours ; refresh failures range from 1 hr to 2 hrs over 13 days

3) all data sources in both are set to 3 hr timeout

4) desktop refreshes work

5) error ":{"type":1,"value":"Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}}],"exceptionCulprit":1}}} 

[v-xicai · Accepted solution]

Hi @Anonymous ,

Firstly, if your dataset is too large,  it will fail to refresh until you filter the data model back down under 1 GB.

Secondly, you may improve the performance for the data model, see Power BI performance best practices , Power BI Performance Tips and Techniques.

In addition, you may specify appropriate value in “Command timeout in minutes (optional)" following steps below, then check if you can  refresh successfully in Power BI Desktop.

1. Click on the arrow for "Edit Queries" in your current Power BI Desktop file, select "Data Source Settings” in the dropdown.

2. Click on "Change Source..." in the Data source settings pop-up window.

3. Click on Advanced Options in the pop-up window, enter 120 minutes in the "Command timeout in minutes (optional)" textbox, then click OK.

For example in the SQL Server connection page:

You may also refer to the article: SQL SERVER – Timeout expired. The timeout period elapsed prior to completion of the operation .

Best Regards,

Amy 

Community Support Team _ Amy

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

[lbendlin]

Note that #3 only impacts the Connection timeout, there is currently no way to set the Command timeout in Power BI.