Memory Allocation failure: The paging file is too small for this operation to complete

[Anonymous]

Hello,

I'm getting Memory Allocation Failure error while refreshing the data. It was working fine when I refreshed for 167 million rows.

My source is Azure Sql DB and I'm using native queries to import the data. I have 12 column and 195 million rows.

Query:

select * from table1 where date = (select max(date) from table1)

union all

select * from table2 where date = (select max(date) from table2)

union all

select * from table3 where date = (select max(date) from table3)

All the tables have same structure

Error:

Failed to save modification to the server. Error returned: 'Memory error: Memory Allocation failure : The paging file is too small for this operation to complete. Try simplifying or reducing the number of queries. Memory error: Memory Allocation failure. Try simplifying or reducing the number of queries.

[hnguy71]

The most likely culprit is that you have too much data when you're importing. Try removing columns / rows that you may not need in your table and refreshing. Also, check to see if you have relationships set if not you may have an underlying cartesian product

[Anonymous]

Hello hnguy17,

I have 12 columns and 195 million rows

[hnguy71]

but do you really need all that 195 million rows? Can you pre-filter only the information that you need? You could possibly be reaching a hardware limitation. Try publishing the report on Power BI service if the file is not too large (>1 GB)

[TomMartens]

Hey @Anonymous,

wondering if you are using native queries to import the data to power query or if you are using the direct query mode to connect to the database.

Try DAX Studio to detect what and how many queries are issued by Power BI Desktop.

Regards,
Tom

[Anonymous]

Hi @TomMartens 

I'm using Import Mode, it was working fine when I refreshed for 167 million rows but now its showing error for 195 million rows.

The query I'm using to import the data is mentioned below:

Query:

select * from table1 where date  = (select max(date) from table1)

union all

select * from table2 where date  = (select max(date) from table2)

union all

select * from table3 where date  = (select max(date) from table3)

Note: All the tables have same strurcture.

Secondly, its not even refreshing in Power BI Service. I have Power BI Pro and Power BI Premium also.

Error in Power BI Service: Last refresh attempt failed because of an internal service error.

I don't understand, why its failing in Power BI Service, I don't think there is any issue of memory.

[TomMartens]

Hey,

it's difficult to say, what's going on.

Are you on Power BI Desktop 32bit or 64bit?
You can determine the version from Help (menu) --> About (command).

How many RAM is available on your machine?

Does it work if you just use this SQL statement:

select * from table1 where date  = (select max(date) from table1)

If it does work add the 2nd table and so on, 

Have a closer look at the task manager and watch how many RAM is consumed during the refresh, maybe you encounter that out of sudden a process has a problem.

Regards,
Tom

[Anonymous]

Hi @TomMartens 

I have Power BI Desktop 64 bit version(February, 2019)

RAM-> 8 GB

Its working fine when I import data in new pbix with same query (without any visual and measures) but when I refresh in old pbix its showing me an error.

Data in tables:

Table1 -> 13 million

Table2 -> 1 million

Table3 -> 181 million

[Anonymous]

Yes all 195 million records are necessary.

I have Power BI Pro and Power BI Premium, so I think I don't think that I have any issue if I publish the file size of more than 1 GB