Sql Server Compact 4.0 Database Bloat RRS feed

  • Question

  • The Sql Server database that I used to port to the Sql Server Compact 4 database uses 53 megabytes of data and .5 meg of indexes.
    The Sql Server Compact database that got created bloated to 265 megabytes which is 5 times bigger!
    I have tried to compact it and shrink it but it has little effect.
    Does it help to make it readonly (assuming you can do that)?
    FYI, the way the handy/cool tool I'm using is the "Sql Ce 4.0 Toolbox" and was developed by Twitter: @ErikEJ

    John Marsing
    Wednesday, August 10, 2011 12:30 AM

All replies

  • Thanks for the kind words. Keep in mind that all string data in SQL Server Compact is stored as Unicode (double bytes), so it will take twice as much space (and this also applies to inexes based on string columns). But it would be easier to discover why, if you could share you database schema (table definitions) - you can script "CREATE TABLE" statements with the SQL Server Compact Toolbox!
    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    Wednesday, August 10, 2011 6:21 AM
  • Thanks for letting me know about the Unicode but I wouldn't think that would make a difference comparing the two databases as the source database (the Sql Server one that I used to do the import) has these fields defined as nvarchar(max) therefore it would seem it's "apples and apples" when it comes to comparing the two sizes.


    Here is the schema for one of the tables (out of 23) which is by far the largest table...



    -- Script Date: 8/10/2011 7:04 AM  - Generated by ExportSqlCe version
    CREATE TABLE [Scripture] (
      [ID] int NOT NULL
    , [BCV] nvarchar(11) NOT NULL
    , [BookID] tinyint NOT NULL
    , [Chapter] tinyint NOT NULL
    , [Verse] tinyint NOT NULL
    , [VerseText] ntext NOT NULL
    , [WordsKJV] ntext NOT NULL
    , [WordsBHS] ntext NULL


    Here is the script for the same table in the Sql Server database...

    CREATE TABLE [dbo].[Scripture](
     [ID] [int] NOT NULL,
     [BCV] [nvarchar](11) NOT NULL,
     [BookID] [tinyint] NOT NULL,
     [Chapter] [tinyint] NOT NULL,
     [Verse] [tinyint] NOT NULL,
     [VerseText] [nvarchar](max) NOT NULL,
     [WordsKJV] [nvarchar](max) NOT NULL,
     [WordsBHS] [nvarchar](max) NULL,
     [ID] ASC
    ) ON [PRIMARY]


    ALTER TABLE [dbo].[Scripture]  WITH CHECK ADD  CONSTRAINT [FK_Scripture_Book] FOREIGN KEY([BookID])
    REFERENCES [dbo].[Book] ([ID])

    ALTER TABLE [dbo].[Scripture] CHECK CONSTRAINT [FK_Scripture_Book]

    ALTER TABLE [dbo].[Scripture]  WITH CHECK ADD  CONSTRAINT [FK_Scripture_BookChapter] FOREIGN KEY([BookID], [Chapter])
    REFERENCES [dbo].[BookChapter] ([BookID], [Chapter])

    ALTER TABLE [dbo].[Scripture] CHECK CONSTRAINT [FK_Scripture_BookChapter]



    I reluctant to give all of the create scripts for all of the tables, but as I said the Scripture table accounts for 90% to 95% of the total size.  I do have a link that shows the names of the rest of the tables and the sizes of each see here.



    I assume that my read-only question is irrelevant to the size?



    John Marsing
    Wednesday, August 10, 2011 2:36 PM
  • And you are sure about your SQL Server numbers??
    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    Friday, August 12, 2011 6:14 AM
  • ErikEJ,

    I added three more files to my sky drive for you to look at if you wish 1) DiskUsage, 2) DiskUsage_TopTables & 3) IndexPhysicalStatistics that shows that my source data base is about 60 meg.

    You obviously have more experience working with the compact database than I do and seem surprised by my situation, so I infer that this is not a common problem.

    When I have time maybe I will experiment with different sample size to see what kind of compact database sizes I get.

    In the end it's not that big of a deal I mean after all my solution still works and it's not costing me extra money from my web hosting provider for have this large file. Maybe I'm abusing the compact database in that maybe it wasn't really meant to hold that much info???



    John Marsing
    Friday, August 12, 2011 5:44 PM
  • John,

    Can you try running your SQL Server to SQL CE migration experiment using MS Deploy 2.0? And if you are running into the above size issue, open a bug through Microsoft Connect ( with the details?



    “This posting is provided "AS IS" with no warranties, and confers no rights”.
    Thursday, September 8, 2011 6:00 PM
  • Imran,

    I’ve never used MS Deploy 2.0, so excuse my ignorance, but if I do as you say, will this directly answer my question.  My question is not how to deploy my application, as I have done that successfully by right clicking the solution and clicking Publish.  My question is when I create a Sql Server Compact 4.0 database from a Sql Server Database (using Sql Ce 4.0 Toolbox) it bloats to five times its original size. 

    So Imran, are you telling me that at some point in the MS Deploy process one of the steps will be that it will convert a Sql Server database and export the content into a Sql Server Compact 4.0 database and that I will be able to see if there is this excessive bloat? 

    Regardless of your question I have other issues...


    When I first developed my application I was using the free MS Web Developer. Later on I wanted to create a data access layer (DA) by using the VS 2010 VB Express version but I found out that this can’t be don’t.  see the thread Sql Server Compact 4.0 Integration in VS 2010 ExpressSince then I have acquired a MSDN Ultimate subscription and this allowed me to install VS 2010 Ultimate which I did.  I later uninstalled VB Express and C# Express, but did not uninstall the Visual Web Developer 2010 Express.

    So I thought I would do my experiment by creating my DAL using the VS 2010 Ultimate which is what I wanted to do before.  Unfortunately this has created another problem, when I try to create a new connection the "Change Data Source" dialog pops up and SQL Server Compact 4.0 is not in the “Data Source” list box. 

    I did some research and found that SP1 was needed in order to use SQL Server Compact 4.0 and that I should use the Web Platform Installer 3.0 to do that.  This seemed odd because when I initially installed VS 2010 Ultimate, it went ahead and installed SP1 which I confirmed by looking at the Help About.  The next thing I did was fire up Web Platform Installer 3.0 just for fun and it didn’t recognize that Visual Studio 2010 SP1 was installed.  I was a bit confused but I figure maybe if I installed it using WPI 3, then maybe VS 2010 Ultimate would recognizeSQL Server Compact 4.0.  

    Well it didn’t.

    If I try to add a new SQL Compact 4.0 item to my DAL project I get this message….

    “The version of the Microsoft SQL Server Compact database that you are adding to the project is not supported by the project type. The database will be added to the project, but data tools such as the Dataset Designer will not work with this database.”

    Imran , before I continue with my experiment, I need to get this resolved first, any advice would be helpful.


    Thanks, John

    John Marsing
    Monday, September 12, 2011 4:53 PM
  • The VS Tools for SQL Compact currently supports only web projects. My SQL Server Compact Toolbox add-in can help you overcome some of these limitations.
    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    Monday, September 12, 2011 5:31 PM
  • Thank You Erik,


    I forgot that you have the add-in as I was just using the standalone before.  I also see that I can create a web application or a web MVC application, point it to my SQL Compact 4.0 database and then create an Entity Framework model.  I can then build the project which creates a DLL. 

    So my next question is can I then treat this like a console DAL app and have other applications reference it?  Sorry if this is a newbie type question.

    Thanks, John

    John Marsing
    Monday, September 12, 2011 8:28 PM
  • It will be better to create a Class library project == DLL output, than refrence an Console project == .EXE output. My Add-In enables you to add an 4.0 EDMX file to Class library projects
    Please mark as answer, if this was it. Visit my SQL Server Compact blog
    Monday, September 12, 2011 9:57 PM
  • I just saw the "Add Entity Data Model to Current Project".  I tried it out and it works cool! 


    I tried "Add code generation Item" from the edmx but they don't have the "ADO.Net DbContext Generator T4 template".  Do you know If I can get this from Nuget?  I will search it out.  I will also try your suggestions about creating a library object.


    Thanks a lot

    FYI, I added a couple of more tables to my database and went through the process of rebuilding my Compact 4 database but this time with your Add-In. I'm still getting the bloat (it's over 300 meg now).  I looked for the "maintenance" compact/shrink menu like the one you had with the stand alone version but couldn't find it. 

    Oh well having fun anyway



    John Marsing
    Monday, September 12, 2011 10:16 PM