English translation of this error: DM_GWPipeline_Gateway_SpooledOperationMissing

[dbeavon3]

Can someone translate the following into English?  This is what we see when refreshing a dataset that relies on a gateway datasource.

Data source error:

{"error":{"code":"DM_GWPipeline_Gateway_SpooledOperationMissing","pbi.error":{"code":"DM_GWPipeline_Gateway_SpooledOperationMissing","parameters":{},"details":[],"exceptionCulprit":1}}} Table: Time.

In production we have a cluster of two gateways.  We just added RAM to them and rebooted.  Now we consistently get the error you see above.  The two gateways normally work fine.  The dataset normally refreshes fine.   But today we are having unexpected errors that aren't very meaningful.

I am working with a server team to try to fix this.  Please let me know what they should be doing to troubleshoot.  Also it would be helpful to know whether this is a common error and, if not, what configuration factors are causing it (ie. is it a result of having a fault-tolerant cluster of gateways?  Should we turn one of the servers off and see what happens?) 

Ideally we would not be fixing this type of issue by trial-and-error.  It would be nice to have some documentation that explained the meaning of the error message, and explained what misconfiguration may have caused it.  After a bit of googling, I haven't found the docs for this yet...

[dbeavon3 · Accepted solution]

It has been over a year but I want to share the following English translation of the error DM_GWPipeline_Gateway_SpooledOperationMissing. 

This was shared with me thru the course of a support case with CSS. Quote:

- During a data refresh, Powerbi service sends a number of jobs down to the gateway each associated with a unique async operation id guid.
- The gateway performs those operations and periodically the service will ask the gateway for the status on one of these jobs using that id
- Normally the gateway can find that job using that id and report back, but when the gateway restarts it clears all of the data including the ids from memory
- Then after a gateway restarts, all of those async operation ids are cleared from memory but the service still asks the gateway for the status on that id
- Since the gateway cannot find that id it will throw this exception saying "Gateway_SpooledOperationMissing"

We suggest you to make sure that the gateway is not restarted in the middle of a refresh. Also please make sure the gateway is not reaching 100% system memory usage or it will crash.

</UnQuote>

In our case it turns out that the gateway software was regularly crashing.  Even though the machine was provisioned with RAM according to the minimum specs, the RAM appears to be insufficient for our workloads.

Identifying the root cause of errors is not hard with the help of the gateway logs.  Eg.  In a file such as "GatewayErrors20230808.000000001.log” we are able to find lots of messages such as this: "OutOfMemoryException".

So the mystery of the missing spooled operation turns out to be nothing more than a RAM problem, along with an unhappy gateway servce.  I wish some of these details were presented to the folks that are actually suffering from dataset failures.

The next step in this journey is to find a way to publish these logs regularly to the people who need them.  The gateway logs do us no good at all when they are locked up and hidden from the developers and users who need them.  I'm thinking we just need to zip and export them to a NAS file share every hour or something like that. 
... I sure wish Microsoft had some sort of guidance for this.  As obvious as it is, I haven't found any public guidance from them about these messages.  There are lots of other examples of useless messages which are presented to the end users (another useless message is "DM_GWPipeline_Gateway_MashupDataAccessError").   I'm sure that the logs will always provide better information than the Power BI Service.  These logs certainly can't be any more confusing than what is shown in the service!

[PeaBeeEye]

@dbeavon3  there is an open source repository by Rui Romano that has the architecture and scripts to export gateway logs on a regular cadence, combine them, and report on them.  This may help with the "next step" in your journey! 

 RuiRomano/pbigtwmonitor (github.com)

[dbeavon3]

@v-kkf-msft 

I continue to get this issue (DM_GWPipeline_Gateway_SpooledOperationMissing) on a regular basis, year after year.  Despite updates.

Are you able to share the next troubleshooting step, assuming a customer is up to date on their gateway software?

I'm assuming there are a series of troubleshooting steps that CSS, for example, would use to determine the underlying cause. The last thing I want to do is open a ticket with them right now, but wouldn't mind hearing how they go about troubleshooting this message.

I really wish the error message would be ACTIONABLE.  It is totally useless, especially given the frequency (almost 1 out of 10 refreshes).  The error might as well say "something bad happened.  sucks to be you".  😉

[v-kkf-msft]

Hi @dbeavon3 ,

Power BI refreshes can return large amounts of data, and data is temporarily stored on the gateway machine. This data storage continues until all data is received from the data source. The data is then sent back to the cloud service. This process is called spooling.

Power BI customers might experience dataset refresh failures with error “DM_GWPipeline_Gateway_SpooledOperationMissing”, the issue looks to be intermittent with Oct and Nov version of GW. Customers can download and install the latest release of the gateway, which is available in the download center, contains the fix. That version is 3000.110.5. Download it here: https://www.microsoft.com/download/details.aspx?id=53127 
After downloading, inspect the GatewayInstaller file properties, and check that the version is in fact 3000.110.5.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.