Daily scheduled refresh fails only on Mondays

[NatK]

Curious if anyone has run into this issue with scheduled refreshes. We have a report with a daily scheduled refresh in Power BI Service at 10pm PT. The refresh completes successfully in about 10 minutes every day except on Mondays. Every Monday, we get a timeout error (copied below) and the refresh fails. 

A few things we have tried without success:

We changed the refresh time, originally Mondays at 3am to 10pm.

We got confirmation from IT that the server is not down during that time.

We re-entered the admin credentials as recommended by IT.

Have any of you encountered this? We have other reports refreshing daily without issue. Any insight would be greatly appreciated.

Thank you!

NatK

ERROR:

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"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":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Class","detail":{"type":1,"value":"11"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SQL"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"cp-bcsql01;BC_CPI_PROD"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2146232060"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}},{"code":"Microsoft.Data.Mashup.ValueError.Number","detail":{"type":1,"value":"-2"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}},{"code":"Microsoft.Data.Mashup.ValueError.State","detail":{"type":1,"value":"0"}}],"exceptionCulprit":1}}} Table: Table Name 

[NatK]

Hi @v-yohua-msft ,

Following up on the two tests we ran yesteday, Monday 5/13:

• Use SQL Server Management Studio (SSMS) to run queries manually and observe if they exhibit longer execution times. RESULT:  The queries executed in the same amount of time as they do other days of the week.
• Use SQL Server Performance Monitor and Profiler to look for any signs of resource contention, such as: high CPU usage, memory pressure, disk I/O bottlenecks. RESULT: There were 4 refresh attempts, the first starting at 10:30 AM PT and last at 12:36 PM PT. All timed out. Nothing stood out as the cause of the refreshes timing out. 

This issue remains a mystery. Any further insight would be appreciated.

Thank you,

NatK

[v-yohua-msft]

Hi, @NatK 

Thanks for the reply from @nandic , please allow me to provide another insight:

Because the issue occurs specifically on Mondays, there may be other tasks or workloads scheduled on SQL Server or in your network that align with the Power BI refresh schedule. It would be beneficial to review any scheduled tasks that run on Monday when setting up a refresh.

The error indicates a timeout during the execution of a query against a SQL data source. We recommend that you analyze the performance of the queries involved in the refresh operation. You can use SQL Server Management Studio (SSMS) to run queries manually and observe if they exhibit longer execution times, especially on Mondays. You can check the following link:

Performance Center - SQL Server | Microsoft Learn

If the query does take longer to execute due to increased load or other factors, consider adjusting the timeout settings in your Power BI dataset. This can be done by increasing the timeout value in the dataset settings in the Power BI service. For more information about managing dataset settings, see Configure scheduled refresh:

Configure scheduled refresh - Power BI | Microsoft Learn

Finally, it's helpful to monitor SQL Server's performance at refresh time, especially on Mondays. Look for any signs of resource contention, such as high CPU usage, memory pressure, or disk I/O bottlenecks. Tools such as SQL Server Performance Monitor and SQL Server Profiler can be used for this analysis.

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.

[NatK]

Hi @v-yohua-msft,

Thank you and @nandic  for your input! We will give your suggestions a try this upcoming Monday, and I will followup on the results.

NatK

[nandic]

Did you test dataset refresh from local pbi desktop application at same time to see if the issue is on Power BI Service only or it is up to data source/gateway?

We had issues on Power BI Service recently and the issue was that we didn't update Azure IP ranges that Power BI uses when whitelisting IP addresses on database level.

Other possible reasons:
1) check on database level if query plan is different when refresh is executed
2) are there any other big dataset refreshes that maybe happen only on Monday
3) which license do you use (pro, premium per use, premium)?

Cheers,
Nemanja Andic