none
Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)

    Question

  • I just received this message:

    Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)

    I am completely dead in the water.  Cannot connect to SSAS 2005.  HELP!

     

    Friday, April 28, 2006 1:53 AM

Answers

  • We are actually still working on the issue.  The last recommendation was to move UP to the 64-BIT version of SSAS 2k5.  As we continue to peice-meal the cubes into the 32-BIT solution there is an entire discussion about maintenace and care of the SSAS DB going forward.  Given the application memory "ceiling" that you may be experiencing it may make sense to try the 64-bit version of SSAS 2k5.  This would, as I understand it, help to allocate the required memory in a manner that would potentially allow thw application to run more freely.

    krj

    Sunday, June 11, 2006 1:33 PM

All replies

  • hello,

    it is not quite clear what are you trying to do when the exception happens. Is the system really running low on memory?

    the comment above seems to suggest that you are trying to connect to SSAS, however the exception seems to come from SMO (?) which is an OM for working with SQL server (relational).

    if the issue is that you are unable to connect to MSAS, could you please provide more information as to what are the sequence of actions you take (also how you connect -- from Management Studio or in some different way; if from Management Studio - what do you enter in connection dialog etc.), and more information about when the exception occures and stack trace and other info about the error (in management studio error msg usually has "Show Technical Details" button)?

    thanks a lot,

    Friday, April 28, 2006 7:15 PM
  • I am trying to do the initial load of SSAS 2005.  Just loading the Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe executable to get to the SSAS databases is just stopped.  I had to "split" the SSAS databses to get them imported into three splits.  The SSAS 2000 has 35 dimensions, 49 cubes, and 4 virtual FACT cubes.  This was load #2 out of 3.  I had just completed the cube builds on 31 out of 49 cubes.  Had just completed 138 million rows of populating the SSAS 2k5 database.  I had done tests that put the total row count to 240 to 280 million rows when completed.  The size of the total SSAS DB is currently projected at 16-20 GB when completed.  I tried to re-impliment the 2nd set of cubes and now SSAS 2k5 will not even start.  I had tried the /PAE as well as the /3GB swtiches with no success.  I tried each switch individually with full reboot each.  No success.  --- --- ---.  Help.

     

     

    Wednesday, May 03, 2006 4:09 AM
  • Update.  I took the switch /3GB OUT of the boot.ini and was successful in getting the SS Management Studio to at least start.  Now, as I try to open cubes to work with them in Management Studio, it gives me the following error:

    Excetion of type 'System.OutOfMemoryException' was thrown.  (Microsoft.SqlServer.SmoEnum)

    Is there a 2GB memory limit in SSAS?  When you put in the /3GB switch it actually fails to load initially.  Is there some caching/hashing algorythm that allows the standard non /3GB switch to be successfule and the /3GB switch enablement to fail in high-memory usage cases?  This is a bit perplexing.  The 2000 OLAP database runs at about 1.7 GB TOTAL in production today (the source for the migration wizard), and it is stable.  Using the Database Migration Wizard (9.0.2047.0) I could NOT get it to migrate the whole 2k OLAP database.  It has the memory exception error.  I had to "split" it up (Migtration Wizard) to get the the dimensions and cubes to load by chunking it into SSAS 2k5.  I was successful on the first half of the 2k OLAP migration to SSAS 2k5  mmigrating 31 out of 49 cubes.  I have subsequently loaded all of the dimensions (all 35 from above) with data and cubes (about 31 cubes with about 137 million rows total) with data.  I tried to migrate the other outstanding 16 cubes and all errors started happening and SSAS 2k5 Management Studio is failing to even load.  My hardware/OS config is as follows:

    C drive - 10 GB (RAID 1)

    D drive - 8 GB (4095 pagefile is here) (RAID 10)

    E drive - 50GB (RAID 10)

    RAM - 8GB

    OS - Server 2003 Enterprise (Open License Version 5.2 Build 3790.srv_03_sp1_rtm.050324-1447: ServicePack 1)

    DB - SQL Server 2005 Enterprise - SP1/041806 installed 042006 - Full install of all components.  I have stopped all SQL Services accept SQL Browser and SSAS services.

    Does anyone else have this issue with SSAS 2005 and hitting the ceiling-memory limit?

    How can 2000 OLAP run at 1.7 GB and be stable and SSAS 2k5 fails to even load the exact same data using Microsoft's own toolset?

    I am seeking to find the solution to this issue and are on an extremely aggreesive schedule.  HELP!

     

     

     

    Wednesday, May 03, 2006 3:16 PM
  • Okay,

    I have tried something to see if it will work and here is where I am at.  I cleared the transferred SSAS DB from Management Studio completely.  I re-migrated (in three MigratioWizard.exe sessions) most (32 dimensions and 38 cubes) of the existing objects into a new "stand-alone" SSAS 2K5 database.  I then "imported" the now almost fully deployed (all that is left are the FACT tables) SSAS 2K5 into BI Studio successfully using the import funtion in BI Studio!  I then backed up and deleted the SSAS 2K5 DB from Management Studio.  Now the bad news.  I was able to get the BI project to "build" with no errors.  Now, I am back to the SAME Exception of type

    'System.OutOfMemoryException' was thrown

    error I had when only "trying" to partially migrate and populate even 50% of the cubes in Management Studio (see previous postings).  Now, this same error shows up under the errors bottom-left dialog box in BI studio with no other explanantions.  I have been combing the internet trying to get a solution.  Can anyone out there help??

    Wednesday, May 03, 2006 11:34 PM
  • We have a defect in RTM and SP1 builds, that OutOfMemoryException occurs when SQL Management Studio connects to AS2005 with large database(s). We are fixing this for SP2.

    Meanwhile, the work-around would be to use Visual Studio to connect to AS2005 to administer or process objects.

    Or to temporary separate databases on different AS2005 instances (then script each of them and run the scripts to combine them back on a single AS2005 instance).

     

    Adrian Dumitrascu.

    Thursday, May 04, 2006 12:41 AM
  • The deimensions and cubes are now in ONE instance of BI Studio 2005.  There are no more SQL or ANY databases that are in this box or connected to this instance.  I am running a DELL 2850 with DUAL 3.8 Ghz procs, 8GB or RAM, and ALL data partitions that SQL 2K5 touches are RAID 10/15k hard drives.  This should be "flying" on 2K5 from all of the press.  These databases are running perfectly on SQL 2000 AS at 1.7 GB of RAN with no issues.  I want to be crystal-clear with my response.  The objects are ALL now in BI studio (definitions of only dimensions and cubes) and the SQL 2K5 BI studio throws the SAME error whether I try to use the migration tool OR try to deploy using BI Studio.  I need help or this is going to be a Microsoft 2K5 disaster.  Is there some work around to data sizes coming from 2000 AS?  I cannot be the ONLY person that has seen this complete stoppage.  If you wish to continue on this most-excellent quest to actually see the realization of the intent of "Project-REAL" then please give me a ring at 512.845.4950 or reply to this so that we can overcome this very intense (and complete stoppage) road block.  Please help.  I do not mean to be forward and apologize.  This project should have taken approximately 60-80 hours and it is now well into the hundreds of hours.  I am merely a fellow MS-er just trying to overcome adversity and see this through to success for us all.

     

    :)

     

     

    Thursday, May 04, 2006 1:38 AM
  • I will investigate this and will try to reproduce it on our side to debug and see if we can provide a work-around or a quick fix.

    You migrated the AS2000 database with Migration Wizard ? If yes, can you run the wizard again to generate the migration script ? (there is an option on the first page to either migrate to AS2005 or generate the script). Once you have the script, please archive it as .zip and send it to me at adumitrascu at hotmail dot com.

    Adrian Dumitrascu.

    Friday, May 05, 2006 4:45 PM
  • I forgot to mention, please double check and remove sensitive meta-data in the AS2000 database and the migration script. Especially verify the connection strings for data sources (for passwords - just change the connection strings in the script to bogus values, I don't need them to be correct). Also, if you have MDX calculations that you want to keep private, please remove them from the script (search for "<Commands>" in the script to review them).

    Adrian Dumitrascu.

    Friday, May 05, 2006 4:52 PM
  • Adrian,

    Thanks again for your help here.  You have been great.  We do have some issues with running out of memory that I will be posting.  Here is the error:


    Errors and Warnings from Response
     Memory error: Allocation failure : Not enough storage is available to process this command. .
     Memory error: Allocation failure : Not enough storage is available to process this command. .
     Errors in the metadata manager. An error occurred when loading the AggregationDesign aggregation design, from the file, '\\?\E:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Business Report System ADW.1.db\TA_2003.0.cub\TA_2003.0.det\AggregationDesign.0.agg.xml'.
     Error Code = 0x8007000E, External Code = 0x00000000:.

     

    What can I do now about this?  We seem to be hitting every roadblock that can be during this POC.  Any assistance will be greatly appreciated.

    krj

    Monday, May 22, 2006 3:50 PM
  • Hi Kjr,

    I have the same problem that you had before the last post.

    What solution you had about this problem System.OutOfMemoryException with MigrationWizard? You can help me?

    tks,

    Eder F. Dias

     

    Friday, June 09, 2006 6:54 PM
  • We are actually still working on the issue.  The last recommendation was to move UP to the 64-BIT version of SSAS 2k5.  As we continue to peice-meal the cubes into the 32-BIT solution there is an entire discussion about maintenace and care of the SSAS DB going forward.  Given the application memory "ceiling" that you may be experiencing it may make sense to try the 64-bit version of SSAS 2k5.  This would, as I understand it, help to allocate the required memory in a manner that would potentially allow thw application to run more freely.

    krj

    Sunday, June 11, 2006 1:33 PM
  • Thanks Kjr for answer.

    Actually my environment is:

    - Intel Xeon CPu 3.4 GHZ, 3.93GB RAM

    - Windows 2003 Enterprise x64 SP1

    - SQL Server 2005 Enterprise x64 (SP1 - build 2153)

    How you can see I working in a 64-Bit Environment, but the exception still happen. I don't know why. Maybe my environment isn't the best?  What can i do for became it better?

    Somethig strange is that the MigrationWizard.exe process work in a 32-Bit even in x64 enviroment. There is another application that work in 64-Bit?

     

     

    More informations about my server:

    OS Name Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition
    Version 5.2.3790 Service Pack 1 Build 3790
    Other OS Description  Not Available
    OS Manufacturer Microsoft Corporation
    System Name SRVBRASIL31
    System Manufacturer HP
    System Model ProLiant DL380 G4
    System Type x64-based PC
    Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
    Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
    Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
    Processor EM64T Family 15 Model 4 Stepping 3 GenuineIntel ~3400 Mhz
    BIOS Version/Date HP P51, 10/10/2005
    SMBIOS Version 2.3
    Windows Directory C:\WINDOWS
    System Directory C:\WINDOWS\system32
    Boot Device \Device\HarddiskVolume1
    Locale United States
    Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
    User Name Not Available
    Time Zone E. South America Standard Time
    Total Physical Memory 4,031.28 MB
    Available Physical Memory 1.70 GB
    Total Virtual Memory 5.69 GB
    Available Virtual Memory 3.23 GB
    Page File Space 2.00 GB
    Page File C:\pagefile.sys

     

    tks a lot

     

    Eder F Dias

     

     

    Monday, June 12, 2006 2:44 PM
  • Adrian,

    You mentioned there is a fix for this coming in SP2.  Is this fix available as a Hotfix now or only as a part of SP2?

    - Steve

    Monday, June 12, 2006 9:30 PM
  • Steve,

     

         I had varying degrees of success and are too trying to keep things running and get them stable.  What I had done to extend the envelope is to try give the "system" as much "resources" as I could.  First, I had 8 GB of RAM to your 4 GB.  This may be a critical point.  Remember, in most cases WIndows needs a memory to run as an operating system.  In my case on the 32-bit platform this was around 600/700 MB for just the OS.  On the 64-bit I am not so sure of the base OS memory requirements.  For SQL 2K5 though (on the 32-bit platform) it was recommneded to me to put in the /3GB switch in the boot.ini for SQL 2K5 to allow for higher memory addressing.  Also there is an AWE switch in "spconfig" (properties of the server in SQL 2K5)  to allow for this memory to be used.

         The reason that I am going over this with you is that it seems you are having the EXACT behavior in the 64-bit version that I ma having in the 32-bit version.  It seemed logical to assume that I was hitting the wall because of the 32-bit addressing limitations (as told to me by Microsoft).  I will continue on the next post.

    Sunday, June 18, 2006 12:58 PM
  • (continued from post above) the odd thing is that without the /3Gb switch would throw the error at around 2.57 Gb of memory usage.  This also occures AFTER the dimensions and cubes are populated AFTER you have migrated too so don't think you are out of the woods yet.  With 8 GB of RAM and the /3GB switch I still died at 3.26 overall RAM usage of physical RAM.  What I had read is that SQL 2K5 (Enterprise) still has a 3GB memory limitation at this time.  Remember, as the actual mirroring was enabled in SP1 then maybe they will fix the memory issues in SP2.  I believe that if this issue of memory (it has basically STOPPED us dead in our tracks) should be highest on Microsoft's list of to do's.  I was told to go to 64-bit and ALL of my issues would go away.  Seeing as you are ON 64-bit right now and have the SAME issues I do I am very interested to see what Microsoft's strategy is next to try to keep market-share in the BI arena.  continue on next post...
    Sunday, June 18, 2006 1:08 PM
  • continued from previous.   Bottom line, I had to monkey with the temp-drive space to allow Windows system to think it had more resources than it did.  I had move the virtual memory size from "system - 4 GB" to upwards of " user defined - 20486MB".  The odd thing, it stayed up about 30 % longer before dying with the 'System.OutOfMemoryException' error.  I know this is not a fix.  But, is does illustrate that MS has an issue (32-bit) with memory management on SQL 2K5 with larger BI migrations from SQL 2K OLAP.  I have had no follow-up either on this point from MS. I hop theu haven't forgotten us.
    Sunday, June 18, 2006 1:13 PM
  • The fix for OutOfMemoryException is only part of SP2 (for SQL Development Studio connecting to AS2005 server with large database(s)).

    Adrian Dumitrascu

    Monday, June 19, 2006 4:41 PM
  • Regarding the OutOfMemoryException from MigrationWizard.exe, these work-arounds might help:

    - split the large AS2000 database into smaller ones that MigrationWizard can handle (for example 2 databases each with half the cubes)

    - migrate the smaller databases independently and then merge them back on AS2005; this is where SQL Management Studio will give the OutOfMemoryException on connect (fix coming in SP2), but you can use SQL Development Studio to create offline projects out of the live smaller databases and then do the merging there and deploy at the end (with no processing yet, we'll do that separately)

    OR

    - delete the custom aggregations from AS2000, migrate and then re-create the aggregations on AS2005 using the new aggregations design algorithm; usually this helps if you have many aggregations (they add meta-data, so more memory in MigrationWizard)

     

    Adrian Dumitrascu

     

    Monday, June 19, 2006 5:04 PM
  • For out-of-memory during processing, this helps:

    - set the server property CoordinatorExecutionMode to 1. With SQL Management Studio, right click on the server item and use ‘Properties’. The third property in the list is CoordinatorExecutionMode with a default value of -4 which means that server will process 4 x NumberOfProcs dimension attributes in parallel (so if you have many attribute members, processing them in parallel, even for a single dimension) will lead to out of memory.

    Adrian Dumitrascu

    Monday, June 19, 2006 5:08 PM
  • (re-posting this message, to have it at the end of the thread, easier to find; the original post went somewhere in the middle of the thread)

    Regarding the OutOfMemoryException from MigrationWizard.exe, these work-arounds might help:

    - split the large AS2000 database into smaller ones that MigrationWizard can handle (for example 2 databases each with half the cubes)

    - migrate the smaller databases independently and then merge them back on AS2005; this is where SQL Management Studio will give the OutOfMemoryException on connect (fix coming in SP2), but you can use SQL Development Studio to create offline projects out of the live smaller databases and then do the merging there and deploy at the end (with no processing yet, we'll do that separately)

    OR

    - delete the custom aggregations from AS2000, migrate and then re-create the aggregations on AS2005 using the new aggregations design algorithm; usually this helps if you have many aggregations (they add meta-data, so more memory in MigrationWizard)

     

    Adrian Dumitrascu

    Monday, June 19, 2006 5:10 PM
  • I am getting this error message every time I try to open a MSAS db.. using Visual Studio isn't an elegant solution to this (we ship MSAS dbs to clients and don't want them to have to use BI Studio to browse them... not only that, ALL of their functioning/no memory issue  MSAS dbs are no longer accessible using the Management studio - you can't connect to Analysis Services at all). This is unrelated to migration - I am developing directly for MSAS 2005, but is obviously the same error. Waiting until November for the SP2 CTP and until next year for SP2 (based on this http://prologika.com/CS/blogs/blog/archive/2006/06/18/1293.aspx) is a MAJOR hassle... can't MS release a hotfix for this error? It has rendered Management Studio useless for AS management. We have resorted to usiung XMLA and ascmd to manage the db - something we don't want to push onto our clients. Moving to 64-bit just meant MSAS sucked up 15GB of memory and still had the error (is this a memory leak?! Can we programmatically periodically release the memory?)
    Thanks for any help.
    -cf

    Wednesday, June 28, 2006 1:43 PM
  • Hi folks,

    I got this error from one of our development servers running Itanium 64Bit processors.  It had nothing to do with any of the suggestions made so far, but that's not to say they are invalid.  In my case it was a case of a corrupt database being published onto the server.  I simply deleted the last database (as luck would have it) to be published and redeployed from VSS onto the server and reprocessed.  It worked first time and my error message stopped.  I would suspect you have a corrupt database on the server and should delete it (as long as you can recreate it and reprocess it!).  The corrupt database will deploy and most times even process without error, but believe me when I say it is corrupt!

    Good Luck.  Dave.

    Tuesday, September 19, 2006 4:22 PM
  • I'd like to know if this OutOfMemoryException also existed in the RTM version - prior to SP1?  If so, I may launch production with RTM.  I'd really like to know when SP2 might be available.
    Tuesday, October 03, 2006 2:43 AM
  • I have a machine with 64 bit version of SSAS 2K5, with 12 Gb of RAM and have also the same problem
     
    Thursday, November 01, 2007 11:36 PM
  •  

    I'm having very similar error when I tried to update the roles.

     

    Granting roles a few database by a few database seems to work, but when I try to grant administrator access to all databases, it returns an error message with

     

    'Role' (Role) cannot be saved because of the following errors:

    Error Code = 0x8007000E, External Code = 0x00000000:.

    Error Code = 0x8007000E, External Code = 0x00000000:.

    Error Code = 0x8007000E, External Code = 0x00000000:.

     

    I'm running SQL2005 on Windows 2003 Enterprise with 14 GB memory, and I just installed Hotfix Package 5 for SQL2005 and still have same issue.

     

    Can someone help please!?

     

    Friday, February 01, 2008 2:41 AM