Troubleshooting Out Of Memory error in Anlaysis Server

 Troubleshooting Out Of Memory error in Anlaysis Server

This article discusses how to categorize the cause of and troubleshoot the following error in application event log of Analysis Server. For example:

Event Type:   Error
Event Source:       MSSQLServerOLAPService
Event Category:    Server
Event ID:       123
Date:             3/28/2006
Time:             9:49:33 AM
User:             N/A
Computer:      <Computer Name>
Description:
The server is out of memory.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Sometimes you also will see the additional error as below:

Event Type:   Error
Event Source:       MSSQLServerOLAPService
Event Category:    Server
Event ID:       117
Date:             2006-5-3
Time:             4:11:23
User:             N/A
Computer:      <Computer Name>
Description:
Unexpected fatal error occurred.  Attempting to restart server.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Analysis Services load all dimensions for all databases into volatile memory on startup. During processing, the server consumes additional memory to process updates to dimensions and cubes. If insufficient memory is available for these and other operations, you will receive this message. When this error occurred, it only indicates there is memory pressure at that time. Analysis Service will trigger background process to release memory generally. If this error did not induce the analysis service restart, we can simply ignore this message.

You can resolve this issue in several ways menteiond in the following article:
Error 123
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_as_errors_3s6s.asp

In Analysis Service 2005 (SSAS), we also will see similiar error like this during process operation.
Memory Error: The operation cannot be completed because the memory quota estimate (xxxMB) exceeds the available system memory (xxxMB)

Before Cube processing, SSAS will estimate the required memory size and does memory quota limit check, if the available memory is not enough, the error will occur. To resolve such issue, you can follow the suggestions in the above artile in Analysis Service 2000.

Also, there is a known issue as below
914595 FIX: Dimension processing may fail, and you may receive an error message in SQL Server 2005 Analysis Services
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914595

To work around this problem, we can override the memory quota limit check by setting MemoryLimitErrorEnabled=0 in the config file (msmdsrv.ini): MemoryLimitErrorEnabled=0. After disable the memory quota limit check, SSAS will do the memory check before process so that we will not encoutner this error again.

To troubleshoot such issue we need collect following information:

1. Confirm whether the OOF error is related to MDX query or Process operation.
2. The Anlaysis Service database size, need check the size of the database folders, which are under c:/Program Files/Microsoft Analysis Services/Data.
3. Whether SQL and Analysis Server running on the same box.
4. What is the version of the file msmdsrv.exe, which is under C:/Program Files/Microsoft Analysis Services/Bin by default.
5. Open the Regedit utility, export the values under the
“HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/OLAP Server” sub-tree into a file and get them for further analysis.
6. Collect performance log
7. If the OOF error is related to process log, we need processing log for detailed checking.
8. When the processing log and performance log is enabled, please reproduce the same error (Out of memory). Then provide the following information for research:
a. Performance log
b. processing log