none
Creating Excel Sheets Casues Out of Memory Error RRS feed

  • Question

  • Excel 2007.  VS2010.  .Net 3.5SP1
    I have an Exel add-in that creates new sheets, creates PDFs of them, and then deletes those sheets.  After a a couple dozens sheets, I begin to get this error (this is the full ex.tostring):
    System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
    Server stack trace:
    Exception rethrown at [0]: 
    I have been playing with which metrics to monitor and how to resolve.  My understanding is that the best item to watch is:
    System.Diagnostics.Process.GetCurrentProcess.WorkingSet64
    and that I have seen get as high as 1400MB at times, but sometimes I get the out of memory error when WorkingSet64 shows only 800MB.
    I was not able to find exactly what is it that causes the out of memory (it is clearly not WorkingSet64 hitting a certain number).  Anyone have advice on what I can monitor (and then try to reduce) to avoid this error?
    Wednesday, December 7, 2011 3:41 PM

All replies

  • Just a guess but you might try saving the workbook periodically.  And another thought is if you're using the Track Changes function you might turn it off before you start adding and deleting worksheets.
    Kind Regards, Rich ... http://greatcirclelearning.com
    Thursday, December 8, 2011 3:27 AM
  • Hi Burkeden,

    Thanks for your post.

    You probably didn't release resource of new created sheets on the loop, which might cause the growing memory consumption. Would you like to show us how you manipulate sheets?

    I look forward to hearing of you.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Thursday, December 8, 2011 7:35 AM
    Moderator
  • Calvin - unfortunately I cannot share the exact code: (1) it is confidential and (2) it is >5000 lines. But the process looks like this:

    1. copy an existing sheet (the existing sheet serves as a template)
    2. run mutiple DB queries
    3. put results of queries onto the new sheet
    4. 'print' new sheet as a PDF.
    5. delete new sheet

    My question was really: what specifically triggers the out of memory error? Because the monitor which I think is best to track my apps memory usage shows it at only about 800MB.

    -->i.e. how can I programmatically determine if I am getting close to seeing the out of memory error? 

    Thursday, December 8, 2011 12:48 PM
  • Rich - good suggestions.  That may mitigate the problem.  I will try and get back.  Thanks!
    Thursday, December 8, 2011 12:49 PM
  • Rich - I am not using track changes.  That made me think about clearing the undo list as another way to free memory, but I cannot find a way to do this - other than saving.  The catch is that this application is necessarily a document level add-in.  So while it generates reports, saves them off, and then clear them out of the XLSX with the document add-in, the file itself should not be changed so that it always looks pristine to the users.

     

    Friday, December 9, 2011 5:41 AM
  • Does anyone know what triggers an out of memory error in am Excel add-in?

     

    Is there something I can monitor and at what level is it out of memory?

     

    It is a 4GB machine running 32-bit Excel instance  (Excel 2007 which is of course 32-bit).  No other applications are open and yet I get out of memory when

    System.Diagnostics.Process.GetCurrentProcess.WorkingSet64

    only shows 800MB used.

    Friday, December 9, 2011 5:44 AM
  • Here I got some information about why sometime we get "out of memory" error when a program "only" occupy 800MB of 4GB memory:

    http://www.howzatt.demon.co.uk/articles/OutOfMemory.html

    http://support.microsoft.com/kb/253912

    In addition, I don't think that an Excel process occupy 800 MB memory is normal. Just like what is discussed here:

    Getting Out of memory exception when the memory is not full

    You may need to optimize your add-in to decrease the memory consumption.

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 9, 2011 7:15 AM
    Moderator
  • Calvin thanks.  Those links were informative.  Unfortunately, there apparently is not usually an easy fix for out of memory issues.  It can be any number of resources which are actually running out and even finding which is nontrivial.

     

    I think I will comb through my code again to try to find some objects which are not getting released.

    Friday, December 9, 2011 12:48 PM
  • After a lot of analysis, I can confirm there is no memory leak in the code (or none I can detect after 100s of continuous runs of the application).

    From my logging, I know it is always one of these lines which causes the exception:

    Globals.ThisWorkbook.Sheets("Template5").Copy(after:=Globals.ThisWorkbook.Sheets(1))
                    ws = Globals.ThisWorkbook.Sheets(ws_count)
                    ws.Visible = True
                    ws.Activate()

    This same routine is called dozens (sometimes hundreds) of times in normal production operation.  Most days it is fine and we do not see any errors, but on some it throws the same OOM exception.  I am trying to watch every memory parameter I can find and I cannot even tell what is exactly is exceeding a magic threshold.  It is a terribly uninformative exception.

    It does seem to be specific to one machine (only about 7 run the application).  It happens at least once per week, but I cannot find the magic sequence to make it occur on demand.  I can the same reports with the same data right after seeing the error and will get another error.  I now have the operator restart the PC before running the application in an effort to start from a consistent state each day, but that has not changed the variable results.  That machine does have 4GB of RAM and as I mentioned, no monitor that I have used shows anything actually running out.

    Even though I don't think it is needed, I do frequent garbage collection.

    I am still looking for insight on this!

    Tuesday, January 3, 2012 3:51 AM
  • Are the queries being run against the same machine as the code running?

    Have you tried hiding the pages that are being passed in and saving to clear the undo cache?

    Tuesday, January 3, 2012 6:33 PM
  • Tom,

    The queries are being run on the same machine.  SQLite is the database being used, so it is a local SQL database.

    I am not sure what you mean by hiding the pages being passed in.  And I do not see a good way to save the file -> here is briefly what it does:

    1. user opens XSLX which has the app as a doc add-in
    2. user loads data into SQLite DB (through the app)
    3. user runs many reports.  each of the reports goes through these steps: (1) copies a hidden template worksheet, (2) queries SQLite DB and populates new worksheet with data, (3) saves worksheet as a PDF, (4) deletes the new worksheet leaving only the PDF. 

    I don't want to save the workbook, as I want it to always be in the original state.  Is there a way to clear the undo cache programmatically?  I have looked for a such a feature and not found it.  We do not track changes, so the only history in memory would be related to undo.

     

    Thanks,
    Denis


    • Edited by burkeden Tuesday, January 3, 2012 7:13 PM typo
    Tuesday, January 3, 2012 7:06 PM
  • Denis,

    Did you ever find a solution to your issue? We are experience similar problems.

    Thanks,

    Mark

    Tuesday, January 14, 2014 6:21 PM
  • No mark - no solution and in fact we still get this error today.

    -Denis

    Tuesday, January 14, 2014 7:02 PM
  • Hi Denis

    On skimming through this discussion I have some suggestions. No idea whether they'll be of any more help than what was already mentioned, but:

    1. You were asked about releasing objects; your reply was that you can't share the code. The point wasn't pursued, but it is certainly valid. For every object you declare and instantiate you need to be careful that it is correctly released. Otherwise, all these objects will remain in memory and eventually lead to problems. And clearing the Undo buffer would have absolutely no effect on this problem. The objects would problably (and I emphasize "probably") only be released when the VSTO workbook is closed, forcing everything out of scope so it can be cleaned up.

    2. I get the impression these sheets are being created/destroyed within the VSTO workbook? Given the problems you're seeing, my recommendation would be to create a NEW, independent workbook which you can "destroy" at regular intervals, that might help. There's no reason you couldn't use a template (xlst) as the starting point.

    Indeed, if the only purpose of the VSTO workbook is to act as a template for these sheets you might be better off with an Add-in...


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, January 15, 2014 4:57 PM
    Moderator
  • Thank you for the suggestions Cindy.

    re: Share code. Nothing has changed in this regard. The report associated with running out of memory is approx. 5000 lines of code and it is not something I am allowed to share. I do fully understand the concept of destroying(releasing) objects, and while it is possible I missed something - I have been over this code several times to ensure everything is properly destroyed. And even though not helpful, I have tested putting manual GC events in the code (and that did not seem to help at all).

    re: use a new WB.  My first thought is that it is the same instance of the app code that is running out of memory whether it is creating objects in the original file or in a new file. The objects created and destroyed are all part of the same memory pool - no matter where the sheets are located. Right?

    Wednesday, January 15, 2014 6:40 PM
  • Point 2 is to be considered independently of Point 1. If the problem were due to reusing the same workbook file ("cluttering" it with worksheets, even if you're deleting them) then it could help to have a "disposable" workbook that you can close - without saving, then create a new one (from the template). Do this every ten or fifty or whatever cycles.

    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, January 15, 2014 7:27 PM
    Moderator
  • Hello Everyone,

    I'm also facing OutOfMemory in 32bit version of MS-Office but not in 64bit version of MS-Office. I've also took care of releasing COM object. Marshel.ReleaseComObject and GC.collect as well.

    I've an idea to resolve the problem of working with large amount of data. Let's create a separate process to perform this kind of working. and wait for the process to exit. the reason for this idea is that whatever we do for optimization 32bit process will limit our processing and data. when we start new work-sheet or a new document it consumes some memory from the allotted space. if we initiate a separate process it will have complete space to fetch the data and perform some process on it.

    to resolve your problem, you can do the following.

    1. provide the PathOfExistingSheet and PathOfOutputPDFto the new process, and let it do the reaming steps, when the process gets finish, you can consume the generated PDF
    2. copy an existing sheet (the existing sheet serves as a template)
    3. run mutiple DB queries
    4. put results of queries onto the new sheet
    5. 'print' new sheet as a PDF.
    6. delete new sheet


    Faisal Ahmed Farooqui —————————— If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, December 25, 2014 11:14 AM