SqlCe 3.0: Not enough storage is available to complete this operation.

Question

I'm getting the "out of memory" exception when trying to do various things on a sql mobile [sql mobile 2005] database on a windows mobile 5 device.  Our app uses compact framework 2.0 and is written in c#.

Our application uses strongly typed data sets, and save/loads them from a sql mobile database.  The error occurs in different places, but always somewhere we interact with the database.  It might fail on "DELETE FROM Location" in one run, and on a DataAdapter.Update on another, and a DataAdapter.Fill in another. 

The memory thing I can add to the today screen suggests that when I encounter this error there is only 1-2Mb of program memory free [and about 13Mb of storage].  Unfortunately windows mobile 5 doesn't seem to have a way to adjust the allocation between storage/program memory anymore.

The failure has been occuring while trying to import data [which we are loading from a web service, as weakly typed datasets and copying into our specialized ones].

I have implemented:

• Using statements, and/or dispose statements on:
• Data Adapters
• explicitly calling the dispose method on commands used in data adapters: http://support.microsoft.com/kb/824462
• Sql Connections
• Sql commands [where not used in adapters]
• Using/dispose around the larger data sets in the loading process
• Gc.Collect() before database interaction [this seemed kind of like a last resort]
• Reduction of memory usage [by loading fewer records from the web service at a time]

There does not seem to be a storage memory limitation, a fully loaded database file is about 3Mb.  Our test handheld has only 64Mb of memory, and I've seen at most maybe 25Mb free [storage and program combined]. 

We are opening [and closing] a connection each time we touch the database. How much of a difference will going to a single connection opened when the app starts make?

Any ideas? Perhaps there's something I've missed?

thanks,
-Stair Counter

Answer 1

I would strongly recommend that your application uses only one (1) connection during it's lifetime for all database access.

I have been able to fix this error by making sure only one connection object was used.

Answer 2

Not sure I agree with this approach - perhaps someone from the product team can advise as to whether there is any real benefit to using 1 connection.  If this is a way to resolve this issue (which I doubt very much that it is) it doesn't say much for the capabilities of the .NET CF and SQL CE.

I suspect that your issues are probably to do with the type of data access you are doing.  I have a mobile app that at one point had a 25Mb+ database running on a 64Mb device (my KJam) and it was working just fine.  And yes, I was using multiple connections.

What I would suggest is for you to try to isolate the issue.  In particular if you can build a simplified example of what is causing this out of memory message to occur then we can try to work out what is going wrong.

Answer 3

 

Not sure I agree with this approach - perhaps someone from the product team can advise as to whether there is any real benefit to using 1 connection.  If this is a way to resolve this issue (which I doubt very much that it is) it doesn't say much for the capabilities of the .NET CF and SQL CE.

I suspect that your issues are probably to do with the type of data access you are doing.  I have a mobile app that at one point had a 25Mb+ database running on a 64Mb device (my KJam) and it was working just fine.  And yes, I was using multiple connections.

What I would suggest is for you to try to isolate the issue.  In particular if you can build a simplified example of what is causing this out of memory message to occur then we can try to work out what is going wrong.

Answer 4

Using a single connection worked, however I now have some other memory issues [which I ought to be able to resolve].  That said, I'd have to agree that it seems peculiar that this made a difference.

What tools are there to help figure out what's using how much memory in a windows mobile application?

Answer 5

The Remote Performance Monitor included in.NET CF 2.0 SP1 and improved in .NET CF SP2 should be able to help you. This blog entry is a good strating point (also contains links to installation instructions): http://blogs.msdn.com/stevenpr/archive/2007/03/08/finding-managed-memory-leaks-using-the-net-cf-remote-performance-monitor.aspx

 

Answer 6

set Gc.Collect() after the interactions

Answer 7

I too was experiencing this issue with SQL Mobile 3.5 on WIndows Mobile 6.1. After a few hundred reads/writes I would get the error.

I modified my code to explicitly Dispose the connection and perform a Garbage Collection (I didn't go down the route of using a single connection) and then tested my code successfully to read/write to the .SDF database ~20,000 times without any issues.

finally

{

if (conn.State == ConnectionState.Open)

conn.Close();

conn.Dispose();

GC.Collect();

}

Thanks for the pointers guys.

Regards
David.

Answer 8

I was having lots of SQLCE issues prior to creating one connection, had tried garbage collecting, but that did not help.  Now that I have one connection, I do not get any SQLCE errors and my table adapters are a lot faster when loading.

Answer 9

Funny, now when users leave my app running for a while, they will get this error again.  I am using 1 connection throughout the app and GC when closing the app screen.  Does anyone have any more suggestions?

I read that Microsoft wants us to dispose the commands, but how would one do that when I have created all my adapters via the designer?

Answer 10

I got this error to go away by not filling any table adapters without a filter.  It seems the compact framework has issues with just filling a table adapter with a table.

Answer 11

If you get this error it may not be a memory leak, it may just be that your program is using a lot of memory.  This is what I found:


I received the 'not enough storage available to complete this operation' whenever trying to call DataAdapter.Update() using after using a CommandBuilder on a DataAdapter with a single 'select * from table' query.

I searched the web and came up with the following things to try (some of which are contradictory and superstitious) :

____________________

- I got this error to go away by not filling any table adapters without a filter. It seems the compact framework has issues with just filling a table adapter with a table.

- I would strongly recommend that your application uses only one (1) connection during it's lifetime for all database access. I have been able to fix this error by making sure only one connection object was used.

- Close and dispose connections immediately after use.  While the SqlCeConnection is open, "hidden" native resources are allocated to access the database (query plans, temp data, etc). If opening the connection takes unacceptably long, then you need to find an appropriate balance with caching the connection.

- Reduce the “Max Buffer Size” (in the connection string, from its default value 640KB to for example 256KB): modifications are flushed to the memory more often, but native resources holding process’ virtual memory is less (virtual memory hold by "hidden" native resources).

- You may be exhausting the temp directory. When connecting to the database, please try specifying a temporary file folder in the same storage as the main database file (Temp File Directory) and increase the maximum allowable size (Temp File Max Size) to, say 1024. Try also to increase the Max Buffer Size property to 1024 or larger.

- MSDN. After about two weeks, they discovered a deallocation issue with the command object. Make sure to call dispose. FYI, my emulator continued showing the error randomly, but the Pocket PC device did not.

- Reduce usage of memory intensive data structures: datasets in primis... I know that programming with datasets is easy, especially together with DataAdapters, however they were not introduced for a scarce-memory scenario such as Windows Mobile (at least so far). And datasets are a local duplication of the same data you have on the database: thus, in general they can be seen as a data cache that enhances perceived performances.

- http://blogs.msdn.com/raffael/archive/2008/02/21/netcf-memory-leak-now-what.aspx

____________________

I tried using the Remote Performance Monitor in .NET 3.5 Power Tools but it crashed repeatedly and I couldn't find the simple count of total and free memory.

Eventually... I found you can just click on the mobile Start menu - Settings - System - Memory to see free memory.  On the VS 2008 emulator 20MB of the total 37MB is used without running any programs.  My program reduced the remaining 16MB free memory to 5MB and calling a DataAdapter.Update to a large (1MB) table in an encrypted database in a transaction finally caused it to crash.

So it wasn't actually a memory leak issue, it was simply that my application uses a lot of memory.  When running this on a physical device with 60MB total memory we had no problems.

____________________

P.S. After getting this to work and closing and disposing all connections I encountered a sporadic error where a SQL DLL failed to load, probably because of a Microsoft bug (described in another forum post here : http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/42d12898-ea67-4b41-842e-c04efb81bf29/).

The solution was to never to dispose a SQL connection or set it to null, or the DLLs are reloaded.  I therefore now only use one connection to a SQL CE database, and found this to be most important argument in the controversial discussion on closing connections.

Answer 12

Hi, I had this issue one month ago. Perhaps this error comes from the sqlserverce. Every time I run sql query it crashes. I executed the query within the mobile sql server and the same error. What was the problem? Well, the database is huge and if I do "SELECT * FROM [tablename]" i crashes. What is the solution? Just add "top 100" and be sure that you are using proper indexes in the select statement and where clause. After this fix I had no problems anymore. If this is your situation and you have a big database:

- Add "top 100" or any other small number to your select queries.

- Use indexes

Hope this helps.

---

Same sh1t different day