none
SSDT - June 2013 - EsentVersionStoreOutOfMemoryException

    Question

  • In the below, I’ve set out:-

    1. A business goal we are trying to achieve by using SSDT
    2. The technical problem we have encountered
    3. Some additional context
    4. Some observations
    5. Comments & questions

    The Business Objective

    Create a reliable build process for an extensive number of databases, making as efficient use as possible of available resources, giving developers as much support from a CI process by rapidly turning around compiled, deployed, and fully tested builds.

    The Technical Problem

    When making efficient use of resources, builds will fail on the build server, citing the Version Store Out Of Memory message.

    \SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(863,5): error MSB4018: The "SqlStaticCodeAnalysisTask" task failed unexpectedly.

    ... Microsoft.Isam.Esent.Interop.EsentVersionStoreOutOfMemoryException: Version store out of memory (cleanup already attempted)

    ... at Microsoft.Isam.Esent.Interop.Api.Check(Int32 err)

    ... at Microsoft.Isam.Esent.Interop.Api.JetUpdate(JET_SESID sesid, JET_TABLEID tableid, Byte[] bookmark, Int32 bookmarkSize, Int32& actualBookmarkSize)

    ... at Microsoft.Isam.Esent.Interop.Update.Save(Byte[] bookmark, Int32 bookmarkSize, Int32& actualBookmarkSize)

    To work round this, it is necessary to drastically de-parallelise the build. This results in a significant increase in overall build times. Even then successful builds are not guaranteed (though are more likely).

    Inspecting relevant counters in Performance Monitor (assuming I’m looking at the correct ones) shows:-

    • The values of Database -> Database Cache % Available bouncing around during the build process and regularly dropping below 1%
    • The value of Database -> Database Cache Size (Mb) is fixed at 286 Mb

     

    CONTEXT

    We are running the June 2013 release of the SSDT tools.

    The various .sqlproj files have the element CmdLineInMemoryStorage added to them.

    The start of each .sqlproj is:-

    <?xml version="1.0" encoding="utf-8"?>

    <Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">

      <Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />

      <PropertyGroup>

        <CmdLineInMemoryStorage>True</CmdLineInMemoryStorage>

    Observations

    Since switching to the June 2013 version of the tools and using the CmdLineInMemoryStorage switch, there was an improvement in overall build performance. Nevertheless, the Version Store Out Of Memory message still appeared, preventing builds completing.

    The issue we are encountering seems similar to those documented by others:-

    https://connect.microsoft.com/VisualStudio/feedback/details/754386/vs2010-ssdt-10-3-20225-0-esentversionstoreoutofmemoryexception-publishing-database

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/91a49fb8-a8a5-43ed-b2b5-b90dd19652e0/more-out-of-memory-issues-simply-loading-a-solution

    http://managedesent.codeplex.com/discussions/231266

    http://connect.microsoft.com/SQLServer/feedback/details/756943/ssdt-frequent-out-of-memory-low-virtual-memory-on-solution-load-or-comparison

    http://connect.microsoft.com/SQLServer/feedback/details/749108/msbuild-fails-randomly-with-version-store-out-of-memory-cleanup-already-attempted-errors-msb4018

    The below postings about other tools using the Extensible Storage Engine may be relevant when considering how Microsoft can provide a resolution:- 

    http://stackoverflow.com/questions/5913331/what-to-do-with-jet-errversionstoreoutofmemory-on-jetupdate

    http://blogs.technet.com/b/wsnetdoc/archive/2010/02/12/how-to-modify-the-size-of-your-windows-server-2008-r2-based-dhcp-server-s-jet-database-page-cache-at-runtime.aspx

    http://managedesent.codeplex.com/discussions/405939

    http://www.appassure.com/support/KB/4130302/

    Comments & Questions

    If I have understood the error that is being reported by the build server, and postings about ESE that others have made, the problem I and others are encountering involves some low-level configuration details in the ESE Windows component.

    Some of these settings seem to be exposed in the esent.h file, though what the effects are of editing this I do not know.

    Other tools using the ESE component appear to offer users the opportunity to configure how the relevant tools makes use of this component.

    Given the trend for the adoption of Continuous Integration by the SQL development community, it seems sensible that the toolset should allow users to make use of parallelisation in builds.

    My questions are:-

        i) Is it the case that the errors encountered above would disappear if the ESE cache could be configured / tuned for 

           particular build scenarios?

            a) If so, is there a currently available mechanism for adjusting this cache?

            b) If so and there is not a currently available mechanism, (following what appear to be the approaches adopted by other

                 applications using ESE) can a simple mechanism be put in place allowing such configuration (e.g. a simple xml

                 configuration file sitting alongside Microsoft.Data.Tools.Schema.Tasks.Sql.10.dll)?

        ii) If I have been looking in the wrong direction:-

            a) What is the cause of the error I and others have been encountering?

            b) How can we fix it?

    There is no shortage of memory on our build server and I’d quite happily have a multi-Gb cache for an obscure (from my perspective) Windows component if I can fully parallelise the database builds.

    Thank you for your thoughts and efforts!

    Craig @Work


    Craig_At_Work


    • Edited by Craig_At_Work Thursday, July 11, 2013 8:24 AM Formatting
    Thursday, July 11, 2013 8:23 AM

Answers

  • It seems from the stack trace that the exception is in the static code analysis stage, not in the actual build. Therefore turning off the TSQL static code analysis will allow you to get unlocked and start  figuring out which one of the rules causes the problem.

    -GertD @ www.sqlproj.com

    • Marked as answer by Craig_At_Work Tuesday, July 23, 2013 7:57 AM
    Friday, July 19, 2013 11:48 PM
    Moderator

All replies

  • Hello,

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
    Thank you for your understanding and support.

    Regards,
    Fanny Liu 


    Fanny Liu
    TechNet Community Support

    Thursday, July 18, 2013 8:44 AM
  • It seems from the stack trace that the exception is in the static code analysis stage, not in the actual build. Therefore turning off the TSQL static code analysis will allow you to get unlocked and start  figuring out which one of the rules causes the problem.

    -GertD @ www.sqlproj.com

    • Marked as answer by Craig_At_Work Tuesday, July 23, 2013 7:57 AM
    Friday, July 19, 2013 11:48 PM
    Moderator
  • Hi,

    Thanks for the replies :)

    I'll follow this suggestion, and feed back with results.

    (As an aside, is this the type of issue I should be raising in the forum, or going through a more formal technical support incident route?).

    Craig @Work


    Craig_At_Work

    Saturday, July 20, 2013 4:40 PM
  • Opening a customer support ticket gives you a better SLA for sure, and if it were blocking that is the route I would go. The forum is broadly supported, so many people pitch in, but not everybody is there the whole time. For example I come and go as SSDT is no longer my day job. Anyhow, one of the developers mentioned he discovered a bug in the static code analysis task based on your stack trace, he was going to post it on the forum.

    -GertD @ www.sqlproj.com

    Saturday, July 20, 2013 4:45 PM
    Moderator
  • Hello Craig. 

    I have filed a bug to fix this issue for the Static Code Analysis Task. If you could also file a connect bug it will help us better track this issue (There is a pinned post on filing a connect bug on the top of this forum). As Gert recommended, disabling the Static Code Analysis task should work around this issue. 

    Thanks

    Lonny

    Sunday, July 21, 2013 12:26 AM
  • I've followed your suggestion, and this has improved the build perfomance ( without breakages :) )

    For the moment, when we want static code analysis I can configure particular builds/schedules to include this  while using my work arounds.

    Your comments & help are greatly appreciated.

    Thank you!

    Craig @Work


    Craig_At_Work

    Tuesday, July 23, 2013 8:02 AM
  • Hi Lonny,

    Thanks for your reply.

    I'll follow your suggestion and post a connect bug.

    Craig @Work 


    Craig_At_Work

    Tuesday, July 23, 2013 8:03 AM
  • Hi,

    I opened a issue on Connect, yesterday.

    This has been marked closed as a duplicate.

    I'm not quite sure if this is because there is a genuine duplicate (and I don't have visibility of it), or if it was closed by accident/as result of overenthusiasm (there are no comments given as to why) as it appears to superfically resemble an issue marked as fixed in 2012.

    Can/Should I re-open this issue on Connect?

    Craig @Work


    Craig_At_Work

    Wednesday, July 24, 2013 5:34 AM
  • Tuesday, September 03, 2013 7:54 PM
    Moderator