RE: SQL 2005 SSAS - Cube processing fails due to memory error when mor

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I would do the following:

Move SQL Server to a different machine. Your OS consumes over a gig and
your SQL server can consume the rest of your memory quite easily, leaving
none for Analysis Services.

Get on a 64bit OS if possible. This makes a huge difference.

Try to isolate where the error occurs. To do this, first ProcessData on
your measure group. If that works, then ProcessIndexes. I bet your error
occurs on the ProcessIndexes task. If this is the case, then your
aggregations may be taking up too much memory.

Go to your cube structure tab, and in the Attribute tab under the Dimension
section, make sure each attribute has the AggregationUsage property set to
Default. You can change any back to Unrestricted or Full once you isolate
the issue.

Remove any attributes from your dimensions that are not needed (you can add
them back once you figure out the issue).

Redo your aggregations now that you have fewer attributes in your dimensions
and you change your aggregationusage properties.

When processing your cube, set your MaxParallel to 2. Better yet, set it to
process each transaction sequentially. (The more parallel tasks you run, the
more memory you use). You can go back and try to process in parallel later.


Also, you should install BIDS helper. You can get it at
http://www.codeplex.com/bidshelper. It can analyze your dimensions by
right-clicking on your dimension in BIDS. You would be surprised at how many
errors may pop up that you didn't know about, especially issues with duplicat
names and non-uniqueness.

Jason
http://www.stockpickbloggers.com









"JM" wrote:

Server Config:

-Xeon 3.60GHz

-8 GB RAM

-Windows Server 2003 R2 Enterprise SP2

-SQL Server 2005 Standard Edition SP2 Version 9.00.3050.00

-3GB BOOT.ini switch enabled



Issue:

-The processing of our Sales cube in our Production database (SSAS)
fails with Memory Error.



Normal process:

- Successfully process all dimensions in Production DB (SSAS)

- Restart SSAS service to clear memory

- Process Sales Cube in Production DB (SSAS)



Events related to the beginning of this failure:



For 2 weeks, this would fail every time our ETL would run. Then 2
weeks ago, we deleted our Development DB (SSAS) that was restored from
a backup of the Production DB.



Once that database was deleted, the processing of Sales Cube in
Production DB was successful every time for 2 weeks.



Last week, we restored the Development DB (SSAS) from a backup of the
Production DB. Now, when we try to process Sales Cube in Production
DB, it fails every time.



As you can see above, we restart the service before processing Sales
Cube in Production DB. Also, this is the only object that we try to
process. We never even reference of affect Development DB.



Somehow, the presence of this 2nd SSAS database causes our processing
to fail even though we never try to do anything with it. It's mere
presence on the SSAS instance is causing our processing on Sales Cube
in Production DB to fail.



My questions are:

- Since it is restored from a back up of Production DB, could
Development DB actually be getting processed as well when we specify
to process Production DB...i.e., they are sharing an object link so
that when we specify to process only one, it processes both?

- Is there any memory allocation that happens just because an SSAS
database exists? for example, if no one is querying it and it is not
processing, is it eating up lots of memory?



If you have any other thoughts which have a solution besides "put the
2nd SSAS database on a separate machine", please let me know. Thanks
so much in advance. -John



.

Relevant Pages