Denali: Processing tabular tables: memory error: Allocation failure : Not enough storage is available to process this command

Question

I loaded the "Adventure Works DW Tabular SQL 2012 RC0" solution and managed to deploy the metadata to my "development" instance but 2
of the many tables won't process.

"Internet Sales" and "Reseller Sales" fail with an error message

"memory error: Allocation failure : Not enough storage is available to process this command. . If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine."

I am using a virtual machine running Windows 7 with 3.5Gbs allocated (I allocated 4.5Gbs but it only sees 3.5).

I find it weird to fail on memory since the AdventureWorksDWDenali database is less than 200Mbs...

Any idea?

---

Eric Mamet _ MCDBA, SQL Server 2005 MCTS, MCAD .Net

Answer 1

Have you looked at perfmon/task manager to see if something else is steeling the memory from the machine? What was the max memory setting you set for the database engine? maybe the buffer cache is using all your ram?

Pete

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/

Answer 2

There was no limit on the database memory but I can't see any memory usage spike using Task Manager.

The memory usage is stable at 1.76Gbs

I limited the database memory usage to 500Mbs -> Still fails...

Increased "Memory \ VertipaqMemoryLimit" from 60% to 70% -> still fails

Changed "Memory \ VertipaqPagingPolicy" from 1 to 2 (paging using memory mapped files) -> still fails

---

Eric Mamet _ MCDBA, SQL Server 2005 MCTS, MCAD .Net

Answer 3

Using Task manager in "high speed" updates, it looks like it's falling down when the total memory usage gets to 60% of physical memory

---

Eric Mamet _ MCDBA, SQL Server 2005 MCTS, MCAD .Net

Answer 4

The size of a database does not govern the amount of memory that will get used. There are many other factors that contribute to memory usage in SQL Server. 

From http://windows-tech.info/15/5000c072021a8063.php

The error you getting means Analysis Server estimated amount of memory it would need to perform processing operation and there is not enought memory.
 
What you can do about this:
 
1. Force Analysis Server to disregard the estimate and proceed with processing operation. You might get a memory allocation later down the road during processing. For that change MemoryLimitErrorEnabled server property. For description of server properties see http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 5

Jeff,

These links refer to SSAS 2005 while I am using the Vertipaq engine. Are you sure they still apply?

Eric

---

Eric Mamet _ MCDBA, SQL Server 2005 MCTS, MCAD .Net

Answer 6

As far as I'm aware, you can still force SSAS to disregard it's estimates.  That said, you're probably better off posting your question(s) in the SSAS forum http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/threads 

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 7

Thanks, yes, I'll try to post there

---

Eric Mamet _ MCDBA, SQL Server 2005 MCTS, MCAD .Net