locked
How do I set the Max Database Size? It does not work :( RRS feed

  • Question

  •  

    Hello,

     

    i really need your help regarding the Max Database Size of an SQL Compact Database File (*.sdf).

    I´ve created an sdf Databasefile inside an Compact Framework 2.0 Project in VS 08. Then I´ve opend that databasefile inside an Desktop Project to easily fill that Database with Data and the file is now 124MB of size. When I now want to create indices inside the VS08 Server-Explorer an error occurs that tells me, that the Max Size of the Database has reached.

    So, in the MSDN ist the Connection String Max Database Size mentioned and I created the following Connection String inside my desktop application:

    "Data Source=E:\\privat\\fillDB\\fillDB\\testDB_RMS.sdf; SSCE:Max Database Size=2048;"

    Now my Application opens und closes the Database end quits itself. When I now try to create the indices, the same Error occurs.

    My second step was to change the connection settings in the Server Explorer and I set the Max Database size also to 2048. But that doesn´t change anything.

    Can someone tell me, how I set the Max Database Size, so I can add my indices?

     

    Thank you very much

     

    Thomas 

    Friday, July 4, 2008 7:36 AM

All replies

  • HI,

     

    I think that you must compact your sdf database and recreate it with the new connection string, indicating the new Max database size to either 2048 or other.

     

    Once the database is created, by default the max database size is set to 128 Mb. Changing your connection string doesn't affect so the database file is yet created. By compacting your database you'll create other database file and in this case the max database file will be set.

     

    hope it helpfull,

     

    regards,

     

    Friday, July 4, 2008 8:00 AM
  • Thanks for your help, but it wil not work Sad

     

    I´ve made the following:

     

    SqlCeEngine engine = new SqlCeEngine("Data Source=E:\\privat\\fillDB\\fillDB\\testDB_RMS.sdf; SSCE:Max Database Size=2048;");

    engine.Compact("Data Source=E:\\privat\\fillDB\\fillDB\\testDB_RMS_shrink.sdf; SSCE:Max Database Size=2048;");

     

    after that, a new Database was created "testDB_RMS_shrink.sdf" but when I know connect with the Server Explorer and start to create my Indices the same Error occurs...I really have no Idea what to do, sorry Sad

     

    regards

     

    thomas

    Friday, July 4, 2008 9:36 AM
  • Are you storing any kind of images?? what columns are you trying to index on??

     

    regards,,

     

    Friday, July 4, 2008 10:36 AM
  •  

    No, I have no images stored and I will index 5 numeric and 1 nchar columns.

    Friday, July 4, 2008 10:42 AM
  •  

    Max Database Size is not a database creation parameter. Max Database property is valid for the first connection that is made to database file, after that any connection you open is going to use the properties set by the first connection. You have to specify it in the connection string passed to SqlCeConnection. Compact or creation does not have to do anything with it.

     

    To overcome your problem, close all the connections to the database file (from your application, server explorer etc) else it won't work.

    Then re-open a connection with new value for max database size (max value supported is 4091 i.e. 4091 MB). For devices by default max size is 128 MB and for desktops its 256 MB.

    Do remember to keep the connection open with newer properties so that they are valid across all subsequent connections.

     

    When you are done with your present session, pass these properties again next time you open the connection, you have to do it each time you make the first connection to that database file.

     

    Hope this helps.

    Friday, July 4, 2008 11:27 AM
    Answerer
  • So, I´ve deleted all connections in the Server-Explorer and my application does not run.

    Then I created a new connection to my Databasefile with the Server-Explorer (Selected my File and entered 4091 in the Max Database Size Property Field and the clicked OK).

    Now I navigate to my Table in the Server-Explorer and opened the Table-Schema because I wanted to delete 2 columns. When I hit the OK Button and after a few seconds the same error occurs: "The Size of the Database file exceeds it´s maximum. This Setting will be set by the next simultanious Database Connection. [Required Max Database Size(in MB; 0 if unknown) = 129)] " <- Translated from German to English.

     

    I have absolutely no Idea what to do but thanx for your help so far....

     

    thomas

    Friday, July 4, 2008 12:49 PM
  •  

    Few questions before we set your max db size:

     

    Q.1  What is your db size now?  (Let's assume its x)

    Q.2  How much do you expect it to grow more? (Let's assume its y)

     

    After getting values for above two we get the answer for what will be the max database size after all operations. In this case it will be x+y.

    So when you set you max database size, choose a value which is just above x+y.

     

    Setting 4091 is not a good option always, because it is the boundary value where-in any operation after that will not be executed and there are few bugs around it, like the one you just hit. (I assume it's the same). What was the db size when you actually hit this?

    If your data is going to be more than 4 GB you need to think again, either split data into multiple sdf files or migrate to Sql Express.

     

    To work with SqlCe, you should close all the connections, open a new connection with a reasonable db size in connection string (because the larger you set the db size, a small amount of space is also allocated to maintain database information, on a minimum 4 bytes per 4KBs in the memory).

     

    Do persist this connection, so that you don't face any issues with subsequent ones.

     

    One small way to check if there are any connections open on file can be trying to delete file (after taking backup) or renaming it.

    Monday, July 7, 2008 4:49 AM
    Answerer
  •  

    I have lots of images in my SQLCe DB,  I added this to the conn string.

     

    Max Database Size = 2048; Max Buffer Size = 4096

    Monday, July 7, 2008 4:14 PM