Dataflow stuck on refreshing for hours

[ethanluo]

I have a urgent issue with the dataflow refresh in workspace.

Many Dataflows in workspace have been refreshing continuously for several hours (normally it only takes a few minutes) and never stop. I try to cancel it and refresh the dataflow, but it still can't stop for a long time.

How can i fix it permanently?

Any advice would be appreciated.

[v-pnaroju-msft · Accepted solution]

Thankyou, @lbendlin, for your response.

Hi @ethanluo,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, if a dataflow is stuck refreshing for hours and cannot be canceled via the user interface, it may be caused by complex queries, large volumes of data, or issues related to source connectivity.

Please follow the steps below, which may assist in resolving the issue:

1. If canceling via the user interface is ineffective, please utilize the REST API.
2. Review the dataflow refresh history, the Power BI Metrics app, and the gateway logs (if applicable) to identify any bottlenecks.
3. Simplify queries by removing unnecessary steps or columns early in Power Query.
4. Split large dataflows by breaking complex flows into smaller segments, and reduce the chaining of dataflows.
5. As an alternative, you may automate the cancellation of long-running dataflows (e.g., those exceeding 2 hours) using Power Automate.

Additionally, please find the relevant links below:
Dataflows - Cancel Dataflow Transaction - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Dataflows - Get Dataflow Transactions - REST API (Power BI Power BI REST APIs) | Microsoft Learn

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[v-pnaroju-msft]

Hi ethanluo,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi ethanluo,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi ethanluo,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi ethanluo,

Thank you for your follow-up.

Based on my understanding, since multiple dataflows(even small ones)are stuck during refresh, and considering that you are using a SQL Server database, the likely cause could be related to gateway performance, configuration, or connectivity issues.

Kindly follow the steps mentioned below, which may help resolve the issue:

1. Verify CPU usage, memory load, and status in Manage Gateways.
2. Ensure that the latest version of the Gateway is installed.
3. Optimize the SQL Server by investigating slow queries, locks, or high resource utilization.
4. Improve Power Query performance by simplifying the transformations.
5. For handling high workloads, consider using a Gateway cluster to enable better load balancing.

If you find our response helpful, please mark it as the accepted solution and provide kudos. This will assist other community members who face similar issues.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @lbendlin, for your response.

Hi @ethanluo,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, if a dataflow is stuck refreshing for hours and cannot be canceled via the user interface, it may be caused by complex queries, large volumes of data, or issues related to source connectivity.

Please follow the steps below, which may assist in resolving the issue:

1. If canceling via the user interface is ineffective, please utilize the REST API.
2. Review the dataflow refresh history, the Power BI Metrics app, and the gateway logs (if applicable) to identify any bottlenecks.
3. Simplify queries by removing unnecessary steps or columns early in Power Query.
4. Split large dataflows by breaking complex flows into smaller segments, and reduce the chaining of dataflows.
5. As an alternative, you may automate the cancellation of long-running dataflows (e.g., those exceeding 2 hours) using Power Automate.

Additionally, please find the relevant links below:
Dataflows - Cancel Dataflow Transaction - REST API (Power BI Power BI REST APIs) | Microsoft Learn
Dataflows - Get Dataflow Transactions - REST API (Power BI Power BI REST APIs) | Microsoft Learn

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[ethanluo]

Thank you for reply!

Now I can manually cancel the data refresh, but what puzzles me is that since yesterday, almost all the dataflows in the same workspce have been stuck in a long refresh process (even those with little data). Could you please tell me what might be causing this? Since my data is stored in a SQL Server database, could it be due to the gateway?

[lbendlin]

If your cancelation request is not honored you need to raise a Priority A ticket. 

Dataflows do not have a timeout limit and can rack up enormous amounts of CUs if not monitored closely.  In our company we have a Power Automate based process that terminates all dataflows running longer than 5 hours (in line with the semantic model refresh limit)

 open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi

[ethanluo]

Thank you for reply!

Now I can manually cancel the data refresh, but what puzzles me is that since yesterday, almost all the dataflows in the same workspce have been stuck in a long refresh process (even those with little data). Could you please tell me what might be causing this? Since my data is stored in a SQL Server database, could it be due to the gateway?