Microsoft KB Archive/296076

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

Article ID: 296076

Article Last Modified on 11/14/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Windows CE Edition 1.1
 * Microsoft SQL Server 2000 Windows CE Edition 2.0
 * Microsoft SQL Server 2005 Compact Edition

-



This article was previously published under Q296076



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 or a SQL Server 2005 Compact Edition 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.



CAUSE
You may experience this problem when any one of the following actions occurs during a transaction commit:
 * A SQL Server CE application or a SQL Server 2005 Compact Edition application abnormally closes.
 * The Windows CE device is reset.
 * SQL Server CE or SQL Server 2005 Compact Edition 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 or SQL Server 2005 Compact Edition cache, the database might be left in an unrecoverable state.

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



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 or SQL Server 2005 Compact Edition 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.



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 &quot;CompactDatabase Method&quot; topic in SQL Server CE Books Online states:

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

Keywords: kbprb KB296076

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.