SqlCeEngine/ISSCEEngine: Shrink Vs Compact


 

The difference between these two is much similar to Internal and External Memory Fragmentation. 

From SqlCeEngine.Shrink documentation,

Reclaims wasted space in the database by moving empty and unallocated pages to the end of the file, and then truncating the file.  You can configure a database to automatically shrink by setting the autoshrink threshold option in connection string.  Shrink does not create a temporary database file.

 

From SqlCeEngine.Compact documentation,

Reclaims wasted space in the database by creating a new database file from the existing file.  By creating a new database means, it reclaims the free space between rows. 

 

To be more clear, Shrink claims the pages which are entirely free or unallocated; where as, Compact claims the wasted space with in the page too.  Hence Compact requires creating a new database file. 

 

Empty space with in the page could be as result of:

1)      If there were 5 rows in a page and two of them are deleted

2)      If there was a row in the middle of the page which on update required more space, has moved out of the page (Ex: nvarchar column update)

3)      Etcetera

 

Empty pages and unallocated pages could remain in database as a result of:

1)      All rows in a page are deleted

2)      Whole table is dropped

3)      Etcetera

 

Thanks,

Laxmi Narsimha Rao ORUGANTI

 

Comments (3)

  1. kris_kumar says:

    Is AutoShrink property available for CE SDF databases?

    The documentation link for AutoShrink in the above post seems to point to SQL Server documentation.

  2. olnrao says:

    Hey Kris Kumar,

     Thanks for catching the wrong URL.  I just corrected the URL and the auto shrink option details.

    Thanks,

    Laxmi