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