Forum Discussion

am_nr's avatar
am_nr
Helper I
1 year ago
Solved

Memory issue after data table doubles in size

The main fact table in my PBI report has doubled in size (from ~2GB to ~4GB). It's now about 60,000,000 rows. When I try to refresh this table I get the error message:


Failed to save modifications to the server. Error returned: 'There's not enough memory to complete this operation. Please try again later when there may be more memory available. '.

 

It's by far the biggest table in the report, the next biggest table is ~50MB. It's being imported from SQL Server. There was never an issue with loading this table before.

 

I've tried the following but they didn't make any difference:

- Increasing the "Maximum memory used per simultaneous evaluation (MB)" to 1206MB

- Increasing the "Maximum allowed (MB)" for data cache management to 16GB

- Unticking the auto date/time option

- Changing the query-limit simulation from auto to "Premium capacity" (I have a Pro licence but just wanted to test if this would make a difference).

 

Besides reducing the table size, is there anything else I could try? My report has lots of measures and calculated columns (mainly DAX rather than Power Query added columns) - do I need to try making them more efficient? I'm sure there's probably savings to be made there but I'd rather a simpler fix in the first instance. 

  • Hi am_nr, Apologies for missing you last comment.

     

    It’s possible the issue is coming from the SQL Server side. When Power BI refreshes, it pushes queries back to the database, so if SQL Server is already under memory pressure that can cause the refresh to fail. I’d recommend testing the same query in SSMS or to check for indexing or resource bottlenecks on the database.

     

     

    thanks,

    Prashanth