October 23, 2008 at 6:24 am
Problem Description
We are working on one data warehouse project for one of our client. Data warehouse database is in SQL server 2005. Microsoft Analysis service 2005 is used to create a Cube. Microsoft SQL Server 2005 Reporting Services is used as a reporting tool; the data for the reports is being taken from Cube. While generating some high record (600,000 thousands) volume reports through Reporting Services we get the following error message:
An error occurred during local report processing
An error has ocurred during report processing .
Exception of type 'system.outofmemoryexception'was thrown
On executing the MDX query in SQL Server management studio, we get the below error message.
Executing the query ...
Exception of type 'System.OutOfMemoryException' was thrown.
Execution complete
We found one fix related msmdsrv.exe through http://forums.microsoft.com/msdn/showpost.aspx?postid=1234336&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1
This fix resolves the memory consumption of msmdsrv.exe which is used by analysis service. We downloaded this patch from http://support.microsoft.com/default.aspx/kb/933097/ but it was already applied on our servers and still we are facing the same problem.
In our case msmdsrv.exe is not consuming more memory but the overall memory consumption on the machine is more. Once you run the report the memory remained on the server is around 1.5 GB. The only solution we found so far is restarting the machine to release the memory.
Hardware configuration
Intel (R) XEON (TM)
CPU: 2 Quad 4 processor
RAM: 8GB
HARD DRIVE
Software configuration
Windows Server 2003 Standard 64 Edition
Microsoft SQL Server Enterprise Edition (64-bit)
Data Size
Data warehouse database size for one month:
No of fact tables: 1
Number of rows in fact table: 80 Million
Number of dimension: 26
Average records in dimensions: 6,000 thousands
1 Dimension containing maximum records: 130,000 thousands
Cube database size for one month: 3GB
March 2, 2011 at 7:46 am
Usually happens when you have a textual dimension key or selecting on a wide attribute hierarchy with many members.
March 2, 2011 at 11:44 pm
Also, if the result set is large. Try using the NON EMPTY clause.
Raunak J
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply