CompactDatabase Method (SQL Server Compact)

The CompactDatabase method reclaims wasted space in the local SQL Server Compact 4.0 database. This method can also be used to change the collating order, encryption, and password settings of the database.

Note Note

Unless the destination connection string explicitly specifies a password, the resulting database will be unencrypted, whether or not the source was encrypted.

object.CompactDatabase(SourceConnection, DestConnection)




A string value specifying a connection to the source database to be compacted. An error occurs if the database specified by SourceConnection is already open.


A string value specifying a connection to the destination database to be created by the compaction. An error occurs if the database specified by DestConnection already exists or another file with that name already exists.

HRESULT CompactDatabase(BSTR SourceConnection, BSTR DestConnection);

The SourceConnection and DestConnection parameters specify the properties of the source and destination databases. The following table lists the connection properties that you can specify for each parameter. All other connection properties are ignored.




Indicates the name of the provider to use to connect to the data source. If this property is not specified, the OLE DB provider for SQL Server Compact 4.0 is assumed. An error occurs if the name of the provider in the source string is different from the name of the provider in the destination string.

Data Source

Indicates the name of the database. This property is required for both the source and destination connection information.

Locale Identifier

Indicates the locale identifier (LCID) for the new database. If this is omitted, the destination database will have the same LCID as the system locale.

Locale Identifier specifies the collating order for string comparisons in the database.

SSCE:Database Password

Indicates the password, if the database is secured by a password. The source database password must be supplied if the source database contains a password. If this parameter is omitted from the DestConnection string, the new database will be unencrypted. If you want to remove the password from the destination database, the database password must be set to an empty string. If a blank password is supplied the database will be unencrypted. The database password can be up to 40 characters long.

SSCE:encryption mode


encryption mode

The values for this property are:

engine default


platform default

The default is platform default.

SSCE:Temp File Directory

Specifies the location of the temporary database. If this parameter is omitted from the DestConnection string. Then the current database location is used as the location of the temporary database.

ssce: case sensitive

A Boolean value that determines whether the database is case sensitive. Must be set to true to enable case sensitivity or false for case insensitivity. If not specified, the default value is false.

Note Note
This property is introduced starting with the SQL Server Compact 3.5 SP1 release. For more information, see Working with Collations (SQL Server Compact).

Locale identifier values are supported. Use the unique LCID for the value of Locale Identifier. To find a list of unique LCID values, see Supported Collations (SQL Server Compact). You must ensure that the locale is supported on the device on which the database is being created.

If you do not specify an LCID value when you create the database, SQL Server Compact 4.0 uses the system locale. This differs from earlier versions, which always used the English locale if none was supplied.

Important note Important

Abnormal termination of an application before the Compact operation is completed leaves a temporary database file in the system. This file is named <filename>pack.sdf and must be manually removed.