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.
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.
Intel (R) XEON (TM)
CPU: 2 Quad 4 processor
Windows Server 2003 Standard 64 Edition
Microsoft SQL Server Enterprise Edition (64-bit)
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