locked
SqlCeEngine.Compact() error dealing with large file - Max Database Size has been supplied RRS feed

  • Question

  • Dear all,

    I have a SQL CE database file (ClientData.sdf), 751MB. I need to compact that file and set password on it. I called Compact method of SqlCeEngine for the purpose like below (C#):

    new SqlCeEngine("DataSource=C:\\ClientData.sdf;Max Database Size=2047").Compact("DataSource=C:\\ClientData.sdf;Password=3386706919782613042-6413125869375586174;Encrypt Database=true;Max Database Size=2047");

    I got an exception although the Max Database Size has been supplied there. Here is the exception:
    Message
    "The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only. [ Required Max Database Size (in MB; 0 if unknown) = 733 ]"
     
    Source
    "SQL Server Compact ADO.NET Data Provider"

    However, the .sdf file is successfully added through Server Explorer > Data Connections in Visual Studio. I set the Max Database Size property to 2047 as well.

    Here is my machine specification:
     - WinXP SP2
     - Visual Studio 2008
     - SQL Server 2008 (the .sdf file needs to be synchronized with this database - use SQL 2008 Change Tracking)
     - SQL CE 3.5

    Could you please assist me to solve the problem?
    Appreciate for your help.


    Agung

    Tuesday, October 13, 2009 3:34 AM

Answers

  • Dear all,

    I just found this explanation:

    "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."

    Please see this link for detail: http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/ab19bd83-f59f-4cc8-9aee-9fcbcee4f359

    In my case, I've passed the Max Database Size both to instantiate SqlCeEngine and to call the Compact method (as seen at my previous code). I'm courious whether Max Database Size is not needed on calling the Compact method. By this, I try to compact the database using SQL Server Management Studio instead of the code.

    I do these:

    Connect to the sdf file:

    1. Open program Microsoft SQL Server 2008 > SQL Server Management Studio
    2. at the Microsoft SQL Server Management Studio, click File > Connect Object Explorer
    3. On the Connect to Server form, set the following:
    - Server type: SQL Server Compact Edition
    - Database file: [the sdf file]
    4. press Options>> button
    5. Set Maximum Database Size to 2047MB
    6. press Connect button
     =>as the result, an SQL Server Compact is listed in the Object Explorer

    Note: if I don't set the Maximum Database Size (default is 128MB), the connection would fail. The same exception is displayed.

    Compact the Database
    1. Right Click the SQL Server Compact that just added, select Properties
    2. At the Database Properties form, select Shrink and Repair
    3. Set Perform full database compaction at Shrink and Repair Options
    4. Set Replace existing database at File options
     => be aware that there is no Max Database Size property within the form
    5. Finally, press OK button to do the compaction
     => as the result, I get the same exeption is displayed.

    By this experiment, currently I conclude that Compact method doesn't support Max Database Size value. The value would be ignored what ever it is.

    So my further enquiries will be:
    - What is the result if Max Database Size value is supplied on calling SqlCeEngine.Compact method.
    - Is it similar calling SqlCeEngine.Compact on the code and manually execute database Compaction using SQL Server Management Studio as I did?


    Agung

    Tuesday, October 13, 2009 8:04 AM

All replies

  • Dear all,

    I just found this explanation:

    "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."

    Please see this link for detail: http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/ab19bd83-f59f-4cc8-9aee-9fcbcee4f359

    In my case, I've passed the Max Database Size both to instantiate SqlCeEngine and to call the Compact method (as seen at my previous code). I'm courious whether Max Database Size is not needed on calling the Compact method. By this, I try to compact the database using SQL Server Management Studio instead of the code.

    I do these:

    Connect to the sdf file:

    1. Open program Microsoft SQL Server 2008 > SQL Server Management Studio
    2. at the Microsoft SQL Server Management Studio, click File > Connect Object Explorer
    3. On the Connect to Server form, set the following:
    - Server type: SQL Server Compact Edition
    - Database file: [the sdf file]
    4. press Options>> button
    5. Set Maximum Database Size to 2047MB
    6. press Connect button
     =>as the result, an SQL Server Compact is listed in the Object Explorer

    Note: if I don't set the Maximum Database Size (default is 128MB), the connection would fail. The same exception is displayed.

    Compact the Database
    1. Right Click the SQL Server Compact that just added, select Properties
    2. At the Database Properties form, select Shrink and Repair
    3. Set Perform full database compaction at Shrink and Repair Options
    4. Set Replace existing database at File options
     => be aware that there is no Max Database Size property within the form
    5. Finally, press OK button to do the compaction
     => as the result, I get the same exeption is displayed.

    By this experiment, currently I conclude that Compact method doesn't support Max Database Size value. The value would be ignored what ever it is.

    So my further enquiries will be:
    - What is the result if Max Database Size value is supplied on calling SqlCeEngine.Compact method.
    - Is it similar calling SqlCeEngine.Compact on the code and manually execute database Compaction using SQL Server Management Studio as I did?


    Agung

    Tuesday, October 13, 2009 8:04 AM
  • Please see the KB article no http://support.microsoft.com/kb/971027. Although the KB article refers to the upgrade API the fix is also applicable to the Compact API

    Regards

    Ambrish
    Program Manager. Please hit 'Yes' if my post answered your question(s)
    Thursday, October 15, 2009 12:54 PM
  • Agung

    Yes, your conclusion is correct and this is a known issue;

    We have released a hotfix for this on 3.5 SP1. Refer to http://support.microsoft.com/kb/971027, though this KB article is for upgrade api, internally we use the same method for compact api as well.

     

    Please let us know whether your problem get resolved with this hotfix release.

     

    Thanks
    Manikyam Bavandla MSFT

    Thursday, October 15, 2009 12:58 PM
  • Dear all,

    Thank you for the respond but I'm affraid it didn't solve my problem.
    Actually I've tried that hotfix. Unfortunately, there was an error during installation, i.e. A later version of Microsoft SQL Server Compact is already installed (I installed SQL Server 2008 SP1 instead of SQL Server 2005 in my machine)

    Due to that message, I assumed that there was no problem at all with my installation. But I still can not compact my sdf file.

    Regards

    Agung
    Sunday, October 18, 2009 1:40 PM
  • Dear all,

    I just made an experiment on calling the SqlCeEngine.Compact method by developing a new small application. It has compacted a huge file*) succesfully. I do these:

    1. Generating an SQL CE database file (sdf) using Local Database Cache template (VS 2008 SP1). It accesses an SQL Server 2008 database (source).
    => I get the generator produces an sdf file of size 944MB

    2. Compacting the sdf file into another file without passing any password.
    => It produces a 754MB sdf file. (Success!)

    3. Compacting the sdf file into another file by passing a new password.
    => It produces a 754MB sdf file with password. (Success!)

    4. Compacting the sdf file - into the same name, without password
    => the sdf file is compacted to 754MB. (Success!)

    So that, I conclude that SqlCeEngine.Compact method can be used to compact a huge file. It is  proved on 944MB file producing 754MB (reduce 20%).

    My problem might be raised by another thing. I wonder whether the compact method produces a larger file than the source. I posted a question about this here: http://social.microsoft.com/Forums/en-US/communicationsserversqldatabase/thread/8d8610e8-7e4a-4e2b-b747-ad9fb07ef74c


    Regards,

    Agung


    *) file which is larger than 128MB (default value of Max Database Size)

    Wednesday, October 21, 2009 8:23 AM