SqlCeEngine.Compact method produces a larger file RRS feed

  • Question

  • Dear All,

    I tried to compact a huge*) sdf file , i.e. 751MB into another file. I wrote this code:
    new SqlCeEngine("DataSource=C:\\ClientData.sdf;Max Database Size=2047").Compact("DataSource=C:\\ClientDataCompact.sdf;");

    Finally, I got an exception, i.e.
    "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 ]"
    "SQL Server Compact ADO.NET Data Provider"

    During debugging, I pay attention on the process, i.e. when ClientDataCompact.sdf is created and its size is increasing.  I saw the size increased from 20KB..etc..to 747MB (almost the same with the source, i.e. 751MB). Suddently, the exception appeared.

    The increment makes me curious that the process produced a larger file than the source. So that, the exception was thrown. I'm sure that there is no problem at all with the Compact method on compacting a huge file (ref: http://social.microsoft.com/Forums/en-US/syncgeneral/thread/0a3195d6-d5d2-40e2-89c4-e3963e1be47e)

    Compact method itself has two impacts, i.e.
    1. It stores all table records in order by their primary key
    2. It updates the statistics used by the query processor.
    ref: http://msdn.microsoft.com/en-us/library/ms838028.aspx

    Does the statistic increase the size of the database?
    Is it possible the compact method producing a larger file than the source?  How?
    What's wrong with my sdf file so compacting it produces a large file?



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

    Wednesday, October 21, 2009 8:13 AM