Compacting a SQL Server 2000 CE 2.0 database or a SQL Server 2005 Compact Edition database by using Visual Basic .NET

This article contains a sample program that was developed in Microsoft Visual Basic .NET for compacting a Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) 2.0 database file or a Microsoft SQL Server 2005 Compact Edition database file. The sample program, CompactDB.exe, uses the SqlCeEngine.Compact method to compact the SQL Server CE database. You can use CompactDB.exe to compact a SQL Server CE or SQL Server Compact Edition database in suspect mode.

The internal structure of a database may fragment over time and after much use, resulting in a waste of disk space. If the fragmentation is excessive, performance may deteriorate. To avoid fragmentation and to get better performance, you can compact the database.

You can compact a SQL Server CE database or a SQL Server Compact Edition database by using SQL Server CE Query Analyzer. To do so, select the database, and then click the Compact and Repair icon. However, you cannot compact a database in suspect mode by using SQL Server CE Query Analyzer, because the suspect database may not be displayed under Databases in the Objects tab. In such a case, you can use CompactDB.exe to compact the database.

During the compact process, CompactDB.exe creates a destination file named Comp_Temp.sdf. If the database is compacted successfully, the source database file is deleted and the destination database file is renamed to the deleted source file name.

Before you compact the database, make sure that:

• The source database is not open.
• The destination database does not exist.
• Sufficient storage space is available for both the original and the compacted databases.
• Sufficient storage space is available for the cached data and for the data stored in the temporary database.

Set a Unique Device Name for Pocket PC Emulator

1. Open Microsoft Visual Studio .NET 2003.
2. On the File menu, point to New, and then click Project.
3. In the New Project dialog box, expand Project Types, and then click Visual Basic Projects.
4. Expand Templates, click Smart Device Application, and then click OK.
5. In the Smart Device Application wizard, click Pocket PC under the What operating system do you want to target? section, and then click OK.
6. On the Build menu, click Deploy Solution.
   
   Note Pocket PC 2002 Emulator is displayed.
7. On the Start menu, click Settings.
8. In the Settings dialog box, click the System tab, and then double-click About.
9. In the About dialog box, type a unique device name in the Device name text box, and then click OK.

Upload the Database File to Pocket PC Emulator

Before you run CompactDB.exe, you must upload the SQL Server CE database file that you want to compact in the Pocket PC Emulator. To copy the database file from your computer to the Pocket PC Emulator, in the Pocket PC Emulator follow these steps:

1. On the Start menu, click Programs.
2. In the Programs dialog box, double-click File Explorer.
3. In the File Explorer dialog box, click Open.
4. In the Open dialog box, type the UNC (Universal Naming Convention) path of the database file. For example, \\ServerName\ShareName\FolderName\DatabaseFileName.sdf.
5. In the Logon to Network Server dialog box, type the User name, Password and Domain values to connect to the UNC path of the database file.
   
   Note The User credentials you use should have Write permissions to the folder that contains the database file.
6. Click OK.
7. In the File Explorer window, verify that the database file is listed.

Compact the SQL Server CE Database

To compact a database by using CompactDB.exe, follow these steps:

1. Download the CompactDB.exe self-extractor file from the following location:
   CompactDB.exe (http://download.microsoft.com/download/e/2/4/e2457f11-7a47-43ea-8b09-517123dee21c/compactdb.exe)
2. Double-click CompactDB.exe and extract the contents to a folder.
3. Open the CompactDB.sln file in Visual Studio .NET 2003.
4. On the Project menu, click Add Reference.
5. In the Add Reference dialog box, click the .NET tab.
6. Click System.Data.SqlServerCe from the Component Name list box, click Select, and then click OK.
7. On the Build menu, click Build Solution.
8. If the build process completes without errors, then on the Build menu, click Deploy.
   
   Note On successful deployment, the Pocket PC Emulator is displayed, and the CompactDB.exe program starts.
9. In the SQL Server CE Compact Database dialog box, type the UNC path of the database in the Enter the full path of .sdf file text box.
10. Click Compact.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base: For more information about compacting SQL Server CE databases, visit the following Microsoft Web site: