Thursday, January 1, 2009

Reduce Sql Server Compact Database Size

In most of the cases, the use of Sql Server Compact edition has to do with a Desktop Application where size of the redistributed data do matter.
Having that in mind, we need a way to reduce the size of the database file, so that deployment procedures (possibly through ClickOnce Deployment) wont't need to download a huge amount of data.

As Microsoft states here data Compression on Sql Server Compact is not supported.

Trying to find an alternate way to reduce the database's size i ended up on these functions that Sql Server Compact engine offers through the SqlCeEngine class.
These are the Shrink and the Compact functions.
According to Microsoft:

Shrink
: Reclaims wasted space in the SQL Server Mobile database by moving empty pages to the end of the file, and then truncating the file.

Compact:Reclaims wasted space in the SQL Server Mobile database by creating a new database file from the existing file. This method is also used to change the collating order, encryption, or password settings of the database.

Also:
Unlike the Compact method, the Shrink method does not create a temporary database file. Instead, all empty and unallocated pages are moved to the end of the file and are then truncated, reducing the overall database size.

One must note though that these functions do not compress data, they will just remove unnecessary space claimed from the database system when querying or changing database's schema.

Having that in mind here is a snippet of both the functions

            string sDirectory = Path.GetDirectoryName(txtFileName.Text);
            string sFileName = Path.GetFileName(txtFileName.Text);
            string sNewFilePath = txtFileName.Text+"_old";
            try
            {
                File.Delete(sNewFilePath);
            }
            catch
            {
            }
 
            File.Move(txtFileName.Text,sNewFilePath);
 
 
            SqlCeEngine eng = new SqlCeEngine("Data Source=" + sNewFilePath);
            eng.Shrink();
            eng.Verify(VerifyOption.Enhanced);
            eng.Compact("Data Source=" + txtFileName.Text);

2 comments: