A Windows CE device power failure that occurs during a transaction commit may cause a SQL Server CE database to be marked suspect

View products that this article applies to.

This article was previously published under Q296076

Expand all | Collapse all

SYMPTOMS

A power failure or an explicit reset that occurs during a transaction commit may cause a Microsoft SQL Server 2000 Windows CE (SQL Server CE) database to be marked suspect. You may not be able to recover the database, even if you use the Compact method.

Note The Compact method and the CompactDatabase method are the same methods in SQL Server 2000 Windows CE Edition. The method that you use depends on the development environment that you use. Use the Compact method in applications that are created with Microsoft Visual Studio .NET. Use the CompactDatabase method in applications that are created with Microsoft eMbedded Visual Tools.

Back to the top

CAUSE

You may experience this problem when any one of the following actions occurs during a transaction commit:

A SQL Server CE application abnormally closes.
The Windows CE device is reset.
SQL Server CE stops responding.

If this problem occurs, the database will be marked suspect. A transaction commit includes all the Data Manipulation Language (DML) statements that are not explicitly in a transaction. Depending on the stage of writing data from the temporary database and the SQL Server CE cache, the database might be left in an unrecoverable state.

This problem can occur when you are writing to any storage medium, including RAM.

Back to the top

WORKAROUND

The Compact method will try to repair a suspect database. However, the Compact method may not be able to recover the database.

To minimize the effect of this problem, use the following guidelines:

Make sure that you keep a backup copy of your SQL Server CE database files.
Add a progress bar to your application to prevent users from performing an explicit reset during data manipulation operations.
Keep your transactions short. Use explicit transactions that encompass small amounts of work.

Back to the top

MORE INFORMATION

The following types of corruption may occur:

Logical
Physical

Logical corruption is something that makes the database inconsistent. For example, two pages may reference each other and only one of the pages is correctly written to disk.

Physical corruption is something that damages part of a file to the point that the hardware does not recognize the data.

This article discusses a physical corruption problem.

The "CompactDatabase Method" topic in SQL Server CE Books Online states:

"If SQL Server CE is terminated when writing data because of power failure or a full disk, the data can become corrupted."

Back to the top