Maintaining Databases (SQL Server Compact Edition)
The internal structure of a Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) 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 Edition database.
For more information about using the methods and properties described in this topic, see System.Data.SqlServerCe Namespace Overview.
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 Edition 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.
Note
If the Data Source property is not specified for the destination database, the Compact method overwrites the source database with the new compacted database and has the same name.
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 Microsoft Visual Studio 2005. Compacting a database does not change the identity information in Visual Studio 2005 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.
Important
To use the Compact method, your device must have free space equal to at least double the size of the source 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.
Note
You can also shrink a database by calling the Shrink method. For more information, see System.Data.SqlServerCe Namespace Overview.
Verify
SQL Server Compact Edition database files are divided into logical 4 KB units called pages. As each page is written to the database file, SQL Server Compact Edition 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 Edition 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 SQL Server Compact Edition Engine Object. 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. If the Repair method is invoked with the RepairOption.DeleteCorruptedRows value, all corrupted pages are discarded. This might cause a significant data loss if the corrupted page contains database schema. However, data recovered by using the Repair method should be free from corruption. If the Repair method is invoked with the RepairOption.RecoverCorruptedRows value, the database will try to read data from corrupted pages. This can cause more data being recovered. However, using this option does not guarantee that the data recovered will be free of logical corruption.
Note
The Repair method is useful only if SQL Server Compact Edition returns native error SSCE_M_DATABASECORRUPTED (25017), or if a call to the Verify method of the SqlCeEngine object returns false.
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.
Note
For transactions that must be persisted to the database when they are committed, the application can use the CommitMode enumeration (or DBPROP_SSCE_TRANSACTION_COMMIT_MODE property in OLE DB) to override the default flushing behavior at commit time. By using these properties an application can guarantee that all the transactions that occurred in a database are successfully persisted.
Backup/Restore/Drop
Because SQL Server Compact Edition 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 restore a database, copy the .sdf file back to its regular working location. These operations work even if the database is set up for replication.
- To drop a database, delete the .sdf database file.
See Also
Other Resources
CompactDatabase Method (SQL Server Compact Edition)
Repair Method (SQL Server Compact Edition)