Forum Discussion
Memorable MemberAvoiding the dreadful DM_GWPipeline_Gateway_SpooledOperationMissing in 2024
I'm so tired of the persistent bugs in PBI gateways, including this useless message about a missing spooled operation:
Error: Encountered user gateway exception: '[GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_SpooledOperationMissing [ErrorShortName]=GatewayClientErrorResponseException[GatewayId=89123]/UnknownSpooledOperationIdException [ExceptionErrorShortName]=GatewayClientErrorResponseException[GatewayId=89123]'. RootActivityId = 1d89b6a9-bff7-444e-934d---233543ea8f.Param1 = [GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_SpooledOperationMissing [ErrorShortName]=GatewayClientErrorResponseException[GatewayId=89123]/UnknownSpooledOperationIdException [ExceptionErrorShortName]=GatewayClientErrorResponseException[GatewayId=89123]
Is there a reliable way to avoid this bug in 2024?
Should we be restarting gateway servers once a day?
Should we be transitioning to Gen2 dataflows?
How do we avoid the "spooling" part of the ETL - since this seems to be the most fragile step in the process?
It has been a while since I've tried looking into the cause of this error. If I enabled the enhanced logging and searched my gateway logs, or the event logs, would I be able to discover an underlying trigger that is causing this bug?
Is there any hope that Microsoft will either fix the bug? Or improve the messaging, when the bug happens? It is still shocking to me that such a meaningless and non-actionable message is constantly being shown to the users of Power BI. Even a very technical type of person would NOT have a way to understand this obscure message (let alone the average PBI report developer).
I understand that bugs exist in every software, and I understand that there are unexpected/unpredictable conditions can appear from time to time which may cause software to break. That is not my the source of my complaint. The bigger issue is the fact that PBI will surface a totally meaningless message this frequently, in a language that means nothing to the reader. They might as well just say : "Something bad happened; please try your luck again another day."
7 Replies
- lbendlin
Super User
Try
<setting name="StreamBeforeRequestCompletes" serializeAs="String"> <value>True</value> </setting>in Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config
dbeavon3Hi lbendlin
Yes, I think I remember you mentioning that a year ago or so. I guess I was dragging my feet. As buggy as the default setting is, it scares me to customize the gateway config. (It is also a little annoying to document the recipe for every new gateway that we might build in the future.)
There is another factor. The network team at my company uses this overly sophisticated network path to PBI, that involves a zscaler proxy in Chicago. That netowrk path is the only other variable in play. I am waiting to see what happens when they simplify the path. Although if it was zscaler that is the culprit, I would hope the Gateway's error message would be a little more straightforward. (Even a "connection reset by peer" message would be more useful to me than DM_GWPipeline_Gateway_SpooledOperationMissing).
- dbeavon3
The standard resolution is to get the PBI admin team to call Mindtree support, and they will ask for the same steps each time:
1. restart gateways
2. update gateways.
It isn't likely that there will be a permanent fix. Heck, they probably won't even improve the error message anytime soon.
Another possible solution is to use only one gateway in the cluster. I suspect the bug happens when one machine in the cluster becomes slightly unhappy, and control is transferred to the other. In other words the redundancy of the cluster may be responsible for causing a failure that wouldn't be there otherwise. This is just a guess, and you would have to confirm with Microsoft.
- Poojara_D12
Hi dbeavon3
The "spooled operation missing" error occurs when a Power BI gateway loses track of an in-progress operation, often due to resource exhaustion, network timeouts, or long-running ETL processes. Here's how you can mitigate it:
-
Optimize Gateway Resources: Ensure the gateway server has sufficient CPU, memory, and disk space. For heavy workloads, consider upgrading the server or scaling resources.
-
Restart Gateways Periodically: A daily or weekly restart clears stale processes and can prevent spooling errors.
-
Enable Gateway Logs: Turn on verbose logging in the gateway settings and analyze logs in
%PROGRAMDATA%\On-premises data gateway\GatewayLogs to identify patterns or resource bottlenecks.
-
Simplify Dataflows and Queries: Reduce heavy transformations at the gateway level. Use incremental refresh and partitioning to minimize spooling.
-
Transition to Gen2 Dataflows: Gen2 dataflows reduce reliance on gateways, optimize performance, and handle errors better.
-
Monitor and Debug: Use Power BI diagnostics to trace query performance and correlate errors with system events (e.g., high CPU or timeouts).
By optimizing resources and transitioning to modern solutions like Gen2 dataflows, you can significantly reduce such errors. Let me know if you need help with automation or logs!
- dbeavon3
Hi Poojara_D12
Need to have authoritative references for suggestions like #2.
#2 :Restart Gateways Periodically: A daily or weekly restart clears stale processes and can prevent spooling errors.Can you tell me where this came from, if it wasn't chat GPT?
It is not obvious to customers that the design of this gateway is so buggy that it would require daily or weekly restarts.... Remember that the gateway is implemented in a way that launches PQ mashups as external processes. Even if those misbehave in a substantial way, it should not affect the stability of the gateway as a whole. Assuming you believe that gateways need to be restarted once or more each week, that should be supported with documentation from Microsoft.
- lbendlin
Restarting a gateway is not necessary, but it also should not be a big issue since you are running a multi machine cluster gateway anyway. (You are doing that, right?)
What we found is that the most important setting is the memory limit. Set it to 95% and all the instability issues go away.
-
