Maintaining Databases (SQL Server Compact)

The internal structure of a Microsoft SQL Server Compact database can become fragmented over time, resulting in wasted disk space. If the fragmentation is excessive, performance can deteriorate. To avoid fragmentation, use the following features to maintain the SQL Server Compact database.

For more information about using the methods and properties described in this topic, see System.Data.SqlServerCe.

Compact

You use the Compact method (the CompactDatabase method in native programming) to reclaim space in the database file. You can also use it to change database settings such as password and locale ID (LCID).

SQL Server Compact database files are divided into logical 4 KB units referred to as pages. As a database continues to be modified, some pages might contain unused space, and some pages are unused. Unused pages are eventually reclaimed by the AutoShrink mechanism. For more information, see the "AutoShrink" section later in this topic.

Empty space on pages can be reclaimed only by using the Compact method. The Compact method reads rows from the source database and writes those rows to the destination database, giving the destination database a minimum amount of wasted space.

When you compact a database:

• A new database is recreated and new indexes are created.

• Table pages are reorganized so that they reside in adjacent database pages. This improves space allocation by reducing table fragmentation across the database.

• Unused space created by object and record deletions is reclaimed by rewriting all database data into new data pages. When objects or records are deleted from the database, the space they occupied is marked as available for new additions to the database. Unless a whole page of data has been deleted, the page remains in a partially filled state. The database does not shrink until either the final data is deleted from the page or the database is compacted. For databases in which objects and records are frequently added, deleted, and updated, we recommend that you compact frequently.

• Incrementing identity columns are reset so that the next value allocated will be one step value more than the highest value in the remaining records. For example, if all records in the database have been deleted, compacting the database sets the value of the identity column of the next record to the seed value. If the highest remaining identity value in the database is 50 and the step value is 5, compacting the database sets the value of the next record to 55. This is true even if records that contain values greater than 50 were added previously, but were deleted before compacting. The step value can also be negative, for example –5, and the minimum value is 15. Compacting the database sets the value of the next record to 10.

  Note

  This behavior occurs if you are using the original release version of Visual Studio 2008. Compacting a database does not change the identity information in Visual Studio 2008 SP1.

• If values are specified for the locale identifier or password in the destination database connection string, these values will be used when creating the destination database.

Before compacting a database, ensure that the following conditions are true:

• The database must be closed.

• The destination database must not exist when the Compact method is called. An error occurs if the database specified by DestConnection already exists or another file with that name already exists.

• Sufficient storage space must exist for both the original and compacted versions of the database, in addition to any cached data and data stored in the temporary database.

Autoshrink

To compact a database, you create a new database, and then copy all objects from the source database to the new database. Typically, compacting is not initiated automatically. Automatically adjusting the size of a database file is called AutoShrink. This technique uses almost no processor time and memory, making it especially suited to handheld devices and mobile database products. The Autoshrink technique moves pages within a file so that all the empty or unallocated pages are contiguously positioned at the end of the file. The empty pages are then truncated. Truncated pages are then available for the database file system to use. Returning the truncated pages to the database file system increases file system space.

To set Autoshrink, for managed code, use the AutoShrink Threshold connection string property. For native code, use the DBPROP_SSCE_AUTO_SHRINK_THRESHOLD property. The property specifies the percent of free space in the file before Autoshrink starts.

Verify

SQL Server Compact database files are divided into logical 4 KB units called pages. As each page is written to the database file, SQL Server Compact calculates and saves a checksum for that page. If the page is modified or corrupted after being written to the file, it will no longer match its expected checksum. When SQL Server Compact reads this page, it returns native error SSCE_M_DATABASECORRUPTED (25017).

Calling the Verify method of the SqlCeEngine class recalculates the checksums of every page in the database file and verifies that the checksums match their expected values. If this method returns true, there has been no database file corruption. If this method returns false, the database file has been corrupted and the application should call the Repair method.

Repair

If a database file becomes corrupted, you can try to recover the database file by using the Repair(System.String,System.Data.SqlServerCe.RepairOption) method of the SqlCeEngine object or the Repair method of the native Programming the Engine Object (SQL Server Compact). The Repair method scans the database and calculates the page checksums. If a checksum does not match the checksum that was calculated previously when that page was written to the database, that page is considered corrupted. There are four RepairOptions available. For more information on how these options function, see How to: Verify and Repair a Database (Programmatically)

Autoflush

When changes occur in a database because of transactions, these changes are held in the buffer pool until the transaction commits or aborts. If a transaction aborts, its changes are discarded. If a transaction commits, its changes become visible to other users and transactions, but they might not be immediately written to the database. If there is an abnormal program termination such as a device reset, the transactions that have committed but whose changes have not been written to the database will be discarded.

Note that transactions are always written to the database in the order in which they are committed. This means although some transactions may be lost, the database is always consistent. For example, consider a case in which an application has committed transaction A, and then transaction B. If the application crashes or the device is reset, the database will be in one of three states:

• Unchanged

• Changed by transaction A

• Changed by both A and B

Writing transactions to the database in the order they are committed improves performance by reducing the number of times the database file must be written. Improved performance is particularly noticeable when there are many small transactions that have committed in a short time. In this case, all the transactions are written to the database file at the same time instead of each transaction causing a separate database write operation.

Pending changes in the buffer pool are written or flushed to the database at time intervals specified by the Flush Interval connection string property in ADO .NET (DPROP_SSCE_FLUSH_INTERVAL property in OLE DB). These properties set the maximum number of seconds before committed transactions are flushed to disk.

Backup/Restore/Drop

Because SQL Server Compact is a file-based database system, you can accomplish many common database tasks such as backing up, restoring, and deleting a database by using the file system APIs.

• To back up a database, close all connections to the database, and then copy the .sdf file.

• To drop a database, delete the .sdf database file.

See Also

Reference

CompactDatabase Method (SQL Server Compact)

Repair Method (SQL Server Compact)