How to: Compact a Database (Programmatically)

Article
06/28/2007

In this topic, you will learn how to compact a Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) database by using the Compact method of the SqlServerCe.Engine object and by using the Shrink method of the SqlServerCe.Engine object. The Compact and Shrink methods differ slightly in how they reduce database size.

You use the Compact method to reclaim space in the database file. You can also use it to change database settings such as the password and locale ID (LCID) settings. When you compact a database, a new database file is created, table pages are reorganized so they reside in adjacent database pages, and unused space is reclaimed by rewriting all database data into the new data pages.

You can also use the Shrink method to reclaim space in the database file. However, the Shrink method cannot be used to change database settings, because the Shrink method does not create a new database file; it only reorganizes records and deletes empty records.

For more information on Compact and Shrink, see Maintaining Databases (SQL Server Compact Edition). For more information about using the SqlServerCe namespace, see the SqlServerCe namespace reference documentation.

To compact a SQL Server Compact Edition database

Create an Engine object and pass in the connection string to the existing database you want to compact.
```
SqlCeEngine engine = new SqlCeEngine("Data Source = AdWks.sdf");
```
Call the Compact method. When you call the Compact method, you can also specify new database properties, including adding password protection or encryption.
```
engine.Compact("Data Source=; Password = a@3!7f$dQ;");
```

To shrink a SQL Server Compact Edition database

Create an Engine object and pass in the connection string to the database you want to shrink.
```
SqlCeEngine engine = new SqlCeEngine("Data Source = AdWks.sdf");
```
Call the Shrink method.
```
engine.Shrink();
```

Example

This example compacts an existing SQL Server Compact Edition database and shows how to change database properties.

SqlCeEngine engine = new SqlCeEngine("Data Source = AdventureWorks.sdf");

// Specify null destination connection string for in-place compaction
//
engine.Compact(null);

// Specify connection string for new database options. The following 
// tokens are valid:
//      - Password
//      - LCID
//      - Encrypt
// 
// All other SqlCeConnection.ConnectionString tokens are ignored
//
engine.Compact("Data Source=; Password =a@3!7f$dQ;");

Dim engine As New SqlCeEngine("Data Source = AdventureWorks.sdf")

 ' Specify null destination connection string for in-place compaction
engine.Compact(Nothing)

' Specify connection string for new database options. The following 
' tokens are valid:
'      - Password
'      - LCID
'      - Encrypt
' 
' All other SqlCeConnection.ConnectionString tokens are ignored
'
engine.Compact("Data Source=; Password =a@3!7f$dQ;")

This example shrinks an existing SQL Server Compact Edition database.

SqlCeEngine engine = new SqlCeEngine("Data Source = AdventureWorks.sdf");
engine.Shrink();

Dim engine As New SqlCeEngine("Data Source = AdventureWorks.sdf")
engine.Shrink()

How to: Compact a Database (Programmatically)

To compact a SQL Server Compact Edition database

To shrink a SQL Server Compact Edition database

Example

See Also

Other Resources

Help and Information

Additional resources